Multidimensional Arrays

for RuBoard

Because array elements can store nearly any type of data, they can also store handles to other arrays. This means that you can easily set up multidimensional arrays and that these arrays can be of either the jagged or the smooth variety. Here's an example:

 DECLARE @yhdl int, @xhdl int, @xsiz int, @ysiz int, @res int,         @xcnt int, @ycnt int SELECT @ysiz=20, @xsiz=10 -- Allocate the y dimension SET @yhdl=fn_createarray(@ysiz) -- Allocate and fill each row SET @ycnt=1 WHILE @ycnt<=@ysiz BEGIN   SET @xhdl=fn_createarray(@xsiz)   SET @res=fn_setarray(@yhdl,@ycnt,@xhdl)   SET @xcnt=1   WHILE @xcnt<=@xsiz BEGIN     SET @res=fn_setarray(@xhdl,@xcnt,RAND()*100)     SET @xcnt=@xcnt+1   END   SET @ycnt=@ycnt+1 END -- List each row SET @ycnt=1 WHILE @ycnt<=@ysiz BEGIN   PRINT 'Listing row: '+CAST(@ycnt AS varchar)   SET @xhdl=CAST(fn_getarray(@yhdl,@ycnt) AS int)   SELECT * FROM ::fn_listarray(@xhdl)   SET @ycnt=@ycnt+1 END -- Get a value using x-,y-coordinates SELECT fn_getarray(CAST(fn_getarray(@yhdl,16) AS int),9) AS 'Element at [9,16]' -- Deallocate each row SET @ycnt=1 WHILE @ycnt<=@ysiz BEGIN   SET @xhdl=CAST(fn_getarray(@yhdl,@ycnt) AS int)   SET @res=fn_destroyarray(@xhdl)   SET @ycnt=@ycnt+1 END -- Deallocate the y dimension SET @res=fn_destroyarray(@yhdl) 

(Results)

 Listing row: 1 idx         value ----------- --------------------------------------------------------------- 28.7541 2           89.3502 3           3.53946 4           23.5332 5           86.0147 6           65.5272 7           55.1878 8           28.106 9           54.9643 10          45.7077 Listing row: 2 idx         value ----------- --------------------------------------------------------------- 62.1757 2           88.8092 3           83.9364 4           48.1814 5           46.8372 6           11.51 7           66.9179 8           51.3207 9           87.2797 10          3.83372 Listing row: 3 idx         value ----------- --------------------------------------------------------------- 60.2789 2           4.09385 3           37.9455 4           2.57299 5           52.0562 6           70.8885 7           47.8154 8           54.1449 9           59.3168 10          87.9367 Listing row: 4 idx         value ----------- --------------------------------------------------------------- 24.996 2           94.1695 3           99.9406 4           18.491 5           87.2228 6           30.5012 7           21.4947 8           68.7588 9           78.544 10          80.717 ... Listing row: 16 idx         value ----------- --------------------------------------------------------------- 72.5644 2           11.6483 3           98.446 4           63.0639 5           64.6387 6           71.462 7           18.1232 8           69.4337 9           14.1641 10          12.0571 Listing row: 17 idx         value ----------- --------------------------------------------------------------- 12.8128 2           49.1211 3           44.5183 4           97.7341 5           79.8344 6           94.7446 7           93.0003 8           63.0217 9           31.3682 10          41.8146 Listing row: 18 idx         value ----------- --------------------------------------------------------------- 33.1187 2           23.9623 3           22.8832 4           15.6967 5           88.0725 6           31.4168 7           71.7862 8           99.8463 9           70.8513 10          99.2734 Listing row: 19 idx         value ----------- --------------------------------------------------------------- 66.3026 2           26.2505 3           30.7053 4           16.8188 5           25.4275 6           46.9594 7           39.897 8           36.4633 9           18.7707 10          15.3608 Listing row: 20 idx         value ----------- --------------------------------------------------------------- 57.3852 2           70.897 3           4.85462 4           17.4024 5           28.5141 6           92.8508 7           19.5683 8           50.7395 9           89.062 10          94.2366 Element at [9,16] --------------------------------------------------------------------------- 14.1641 

In this example, we allocate the y dimension of a two-dimensional array up front, then enter a loop in which we allocate each row separately. We fill each row with a series of random floating point values, although we could just as well have stored practically any series of values, including those from tables, as we've seen.

We store the array handle from each row allocation as an element in the y array. This allows us to then loop through the y dimension and list each row. It also allows us to access an element using x-,y-coordinates. Because SQL Server doesn't allow us to implicitly cast a sql_variant as an integer, we have to do so explicitly using CAST(), but this is a minor inconvenience.

It should be obvious by now that you can have as many array dimensions as you want, and that these dimensions can be either jagged (varying numbers of elements) or smooth.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net