Passing An Array Parameter To SQL Server Stored Procedures

SQL Server (2000 & 2005) does not support array parameters for stored procedures. As a workaround, an array of values can be passed into SQL Server as a delimited string.

There are many articles on the web on how to do this, but this is my preferred method because the conversion of the delimited string into table values is done in a reusable function and the function itself can be placed inside a select query and act as a table.

First, we need to convert a delimited string to a table of values. This can be done through the following table-valued function:

CREATE Function [dbo].fnSplit

RETURNS

@table TABLE

(

[Index] int Identity(0,1),

[SplitText] varchar(10)

)

AS

BEGIN

declare @current varchar(10)

declare @endIndex int

declare @textlength int

declare @startIndex int

 

set @startIndex = 1

 

if(@text is not null)

begin

   
set @textLength = datalength(@text)

 

   
while(1=1)

   
begin

   
   
set @endIndex = charindex(@delimitor, @text, @startIndex)

 

   
   
if(@endIndex != 0)

   
   
begin

   
   
   
set @current = substring(@text,@startIndex, @endIndex - @StartIndex)

   
   
   
Insert Into @table ([SplitText]) values(@current)

   
   
   
set @startIndex = @endIndex + 1   

   
   
end

   
   
else

   
   
begin

   
   
   
set @current = substring(@text, @startIndex, datalength(@text)-@startIndex+1)

   
   
   
Insert Into @table ([SplitText]) values(@current)

   
   
   
break

   
   
end

   
end

 

end

 

return

END

To use this function, simply treat it as a table in the query:

select SplitText

from dbo.fnSplit(‘a,b,c’,’,’)

Returns:

a

b

c

The complete process goes like this:

1. Convert the array of values to a delimited string.

2. Pass this string to the stored procedure.

3. Use the above fnSplit function to convert the string to a table of values which can be used in queries.

Posted by Bill Simpkins on October 07, 2008