Array System Functions

for RuBoard

Once the extended procedures have been added to the server, the next thing we need to do is set up system functions to call them. This will make our arrays easier to use and more functional than would be possible with extended procedures only. You'll recall from Chapter 10 that system functions can be created through an undocumented process involving the system_function_schema pseudo- user . We'll create these array functions as system functions to make them available from any database without requiring a database prefix. Here's a script that creates them:

 USE master GO EXEC sp_configure 'allow updates',1 GO RECONFIGURE WITH OVERRIDE GO DROP FUNCTION system_function_schema.fn_createarray, system_function_schema.fn_setarray, system_function_schema.fn_getarray, system_function_schema.fn_destroyarray, system_function_schema.fn_listarray, system_function_schema.fn_arraylen GO CREATE FUNCTION system_function_schema.fn_createarray(@size int) RETURNS int AS BEGIN   DECLARE @hdl int   EXEC master..xp_createarray @hdl OUT, @size   RETURN(@hdl) END GO CREATE FUNCTION system_function_schema.fn_destroyarray(@hdl int) RETURNS int AS BEGIN   DECLARE @res int   EXEC @res=master..xp_destroyarray @hdl   RETURN(@res) END GO CREATE FUNCTION system_function_schema.fn_setarray(@hdl int, @index int, @value sql_variant) RETURNS int AS BEGIN   DECLARE @res int, @valuestr varchar(8000)   SET @valuestr=CAST(@value AS varchar(8000))   EXEC @res=master..xp_setarray @hdl, @index, @valuestr   RETURN(@res) END GO CREATE FUNCTION system_function_schema.fn_getarray(@hdl int, @index int) RETURNS sql_variant AS BEGIN   DECLARE @res int, @valuestr varchar(8000)   EXEC @res=master..xp_getarray @hdl, @index, @valuestr OUT   RETURN(@valuestr) END GO CREATE FUNCTION system_function_schema.fn_listarray(@hdl int) RETURNS @array TABLE (idx int, value sql_variant) AS BEGIN   DECLARE @i int, @cnt int   SET @cnt=CAST(fn_getarray(@hdl,0) AS int)   SET @i=1   WHILE (@i<@cnt) BEGIN     INSERT @array VALUES (@i, fn_getarray(@hdl,@i))     SET @i=@i+1   END   RETURN END GO CREATE FUNCTION system_function_schema.fn_arraylen(@hdl int) RETURNS int AS BEGIN   RETURN(CAST(fn_getarray(@hdl,0) AS int)-1) END GO EXEC sp_configure 'allow updates',0 GO RECONFIGURE WITH OVERRIDE GO 

This script creates six functions. Table 23-2 lists each one and its purpose.

The fn_getarray() and fn_setarray() functions treat array elements as variants, so you can store any data type in an array that can be converted from a variant to a string and vice versa. For example, you can pass a date into fn_setarray() (which will receive it as a variant) and the function will convert it into a string before calling xp_setarray. Likewise, you can retrieve a date stored in an array using fn_getarray() and assign it directly to a datetime variable or column. The function handles the conversion from the string element for you.

Table 23-2. The array system functions

Function	Purpose
fn_createarray	Creates an array (returns the handle as its function result).
fn_setarray	Sets an array element.
fn_getarray	Returns an array element as its result.
fn_destroyarray	Deallocates an array.
fn_listarray	Returns an array as a table.
fn_arraylen	Returns the length of an array.

for RuBoard