« 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://ancientblogs.logos.com/mt-cgi/mt-tb.cgi/257