Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

The Pièce de Résistance

To grasp how powerful this new array functionality is, let's take it out for a spin. Here's a simple Transact-SQL script that uses our new array functionality to create, manipulate, list, and destroy an array:

DECLARE @hdl int, @siz int, @res int
SET @siz=1000

-- Create the array and return its handle and length
SET @hdl=fn_createarray(@siz)
SELECT @hdl, fn_arraylen(@hdl)

-- Set elements 10 and 998

SELECT @res=fn_setarray(@hdl,10,'test10'),
@res=fn_setarray(@hdl,998,'test998')

-- Get element 10
SELECT fn_getarray(@hdl,10)

-- Get element 998
SELECT fn_getarray(@hdl,998)

-- List the array
SELECT * from ::fn_listarray(@hdl)
SET @res=fn_destroyarray(@hdl)

(Results abridged)

 ----------- ----------- 13910056 1000 --------------------------------------------------------------------------- ...