لطفا منتظر باشید ...
Multidimensional Arrays
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.302
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.
