Possible to pass arrays into a stored procedure?

[Ovatvvon]

It would be really beneficial if I could use arrays in the stored procedure, but it doesn't appear as though there is an array data type for variables in SQL Server.

Has anyone else encountered a situation where they needed to use an array, and to filter through it in a stored proc?

-Ovatvvon :-Q

 

[gmmastros]

First, create this UDF

Create  Function dbo.fnSplitter (@IDs Varchar(100) ) 
Returns @Tbl_IDs Table (ID Int) As 

Begin 
 -- Append comma
 Set @IDs = @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
 
 -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
 Set @Pos1 = CharIndex(',',@IDs,@Pos1)
 Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
 -- Go to next non comma character
 Set @Pos2=@Pos1+1
 -- Search from the next charcater
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Now you can use it to parse the array you mentioned.

Select ID From dbo.fnSplitter('1,2,3,4')

You can also use this in stored procedures to filter data.

Create Procedure TempProc
  @IntegerList VarChar(8000)
AS
Declare @Temp Table(Id Integer)

Insert into @Temp
Select Id From dbo.fnSplitter(@IntegerList)

Select Fields
From   RealTable
       Inner Join @Temp Temp On RealTable.IdField = Temp.Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MastermindSQL]

Use XML data as input parameter. With XML you can pass in multiple sets of records.

 

[earthandfire]

xp_array.dll contains extended stored procedures that handle arrays in SQL Server. It is available on the disk that accompanies - The Guru's Guide to SQL Server Stored Procedures, XML, and HTML by Ken Henderson (Addison Wesley - ISBN:0-201-70046-8).

Hope this helps.