Arrays in SQL Server

SQL Server 2005 does not contain arrays, but we can use strings separated by commas to replace them.

 
Create proc testarray
@ Array varchar (max)
As
Begin
If (right (@ array, 1) <> ',') -- ensure that the rightmost is a comma.
Set @ array = @ array + ','

Declare @ start int -- the position where each screenshot starts
Declare @ temp int -- the final position of each clip
Declare @ totallength int -- total length of the input string

Set @ totallength = Len (@ array)
Set @ start = 0;
Set @ temp = 0;

While (@ start <@ totallength) -- if not
Begin
If (substring (@ array, @ temp, 1) = ',') -- test whether the next character is a comma
Begin
Print substring (@ array, @ start, @ temp-@ start) -- If yes, It is intercepted from the last comma
Set @ start = @ temp + 1 -- Update start point, skip the comma
End

Set @ temp = @ temp + 1 -- forward into one
End
End
 

 

ReferenceArticle:

SQL Server 2005 stored procedures do not support array-type parameters

Http://www.cnblogs.com/zys529/archive/2009/02/28/1400258.html