The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

Array System Functions

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.