« Introduction: Bill Simpkins | Main | Keep your WPF UI responsive »
October 7, 2008
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](@text text, @delimitor nchar(1))
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 at October 7, 2008 4:10 PM
Trackback Pings
TrackBack URL for this entry:
http://blog.logos.com/mt-cgi/mt-tb.cgi/257
Comments
This doesn't always matter, but have you compared the performance of this against using XML? Yes, XML is more complicated, but then Microsoft has invested a lot of resources into handling it efficiently. I'm curious if you've read or done your own testing to compare the two.
Posted by: John Fisher at October 8, 2008 12:19 PM
Thank you for bringing this up.
I have found that the performance difference between using this versus OPENXML is very small when there is a small number of values. However, when there is a large number of values, say over 500, OPENXML seems to win in performance slightly. This may vary depending on the actual XML used.
One must also take into consideration the amount of bytes sent to the server. If you want to leave a smaller network footprint, using a delimited string seems to be the way to go.
If performance is critical, it would be best to test both methods on a case by case basis, as there are many variables than can determine performance here.
Posted by: Bill Simpkins 
at October 8, 2008 3:32 PM
Hey guys check this out aprox same with some modifications
http://gsingh840.blogspot.com/
Posted by: Gurpreet Singh at April 12, 2009 5:48 PM
i found this interesting link on code project
http://www.codeproject.com/KB/database/Array_as_Parameter.aspx
Posted by: Enemy of Good at May 19, 2009 2:35 AM