Passing a ​variable-​length string list to a stored procedure in SQL server 2005

Hi Everybody,

I have a problem that I need to pass a list of strings to a stored procedure in SQL server 2005 to be inserted into a table. The problem here is that the list count can not be known in advance. I was wondering if I can pass a datastructure like a datatable ( I'm using C#.Net 2005 to pass the values ) or a string array. If this can be done, then please tell me how to do this. If this can not be done, then what is the best way to pass the list of strings to the SP?

Thanks in advance,
Mahmoud

Matthew van Eerde wrote:

There isn't really a good way to do this in SQL.

In these situations, I find it's much easier to generate the SQL  yourself.  I usually use SP's, but when something like this comes up I generate SQL dynamically.

Loading Image

Click anywhere to cancel

Sql server 2k5 may have a better way to handle this, xml comes to mind, but heres the classic way to do it in just 2k. Use the function below to split a delimitted string and select from the returned table to insert into the table. Its also very helpfull for reporting.

CREATE FUNCTION dbo.SplitString
 (
 @InputString varchar(8000),
 @SplitChar char(1)
 ) 
RETURNS @MyTbl TABLE(_ID varchar(4000))

AS 

BEGIN
 
declare @LENInputString as int
declare @x as int
declare @SingleChar as varchar(4000)

set @LENInputString = len(@InputString)
set @x = 0

while (select @x) < @LENInputString
 begin

 set @SingleChar = SUBSTRING(@InputString, @x, 1)
 
 if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
  begin
  declare @y as int
  set @y = @x+1
  while (select @y) < @LENInputString+1 AND (select SUBSTRING(@InputString, @y, 1)) <> @SplitChar
   begin 
   set @SingleChar = @SingleChar + SUBSTRING(@InputString, @y, 1)
   set @y = @y+1
   end
  set @x=@y
  end

 if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
  begin
  set @SingleChar = ltrim(rtrim(@SingleChar))
  INSERT INTO @MyTbl (_ID) VALUES (@SingleChar)
  end
 set @x = @x+1
 end
return
end

The number of times I've seen this question come up! Seriously, any design that *requires* this is a poor design. What are you doing with the list of values that you can't do in a transaction with multiple sp calls?

Why use this technique? Performance. You make 1 call to the database. Why would it be a bad idea to pass a collection to a stored procedure? Is it a bad idea in .NET to pass a collection as an argument to a method?

Anyways, here's the function I use:

-- SELECT * FROM dbo.SplitList('1 ; 5 ; 2', ';')
ALTER FUNCTION [dbo].[SplitList] (@list VARCHAR(MAX), @separator VARCHAR(MAX) = ';')
RETURNS @table TABLE (Value VARCHAR(MAX))
AS BEGIN
   DECLARE @position INT, @previous INT
   SET @list = @list + @separator
   SET @previous = 1
   SET @position = CHARINDEX(@separator, @list)
   WHILE @position > 0 BEGIN
      IF @position - @previous > 0
         INSERT INTO @table VALUES (SUBSTRING(@list, @previous, @position - @previous))
      IF @position >= LEN(@list) BREAK
      SET @previous = @position + 1
      SET @position = CHARINDEX(@separator, @list, @previous)
   END
   RETURN
END

Thank you all guys for your replies.

The most important issue with my program is performance.

I was about to use CSV, and I searched for a better solution ( as I thout that SQL server 2005 may have introduced such a solution ). But I found that this solution is using XML.

So i was wondering which is better in terms of performance, to use XML to pass the data or to use CSV ?

Thanks.

Look into SQLXML. SQLXML allows you to retrieve and update multiple records of data by sending XML back and forth.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/anch_SQLXML.asp

Whats wrong with a view?

You could pass the return values of that to a proc, and as the view would get all the values, you wouldnt need to know how many there are...

Anything is possible with SQL

If you want to use a variable length separator, like ';;' or '##', you need to modify Tommy's code slightly...

Instead of
SET @previous = @position + 1

use:
SET @previous = @position + LEN (@separtor)

This will make sure the while loop continues at the correct string index.