Wednesday, April 30, 2014

variable count of parameters in a t-sql stored procedure (sql server)


yesterday I faced the problem that I had to push a variable amount of parameters in a stored procedure. In C# we have a keyword called "params".

public void MyFunction(params int[] myIDsToPush)

and call it like this:  MyFunction(1, 2, 43);
I expected that the database supports such a concept too, especially sql server, but unfortunately that was not the case :-( . I needed a work around. 

It took some time to think about possible solutions (and yes of course some google research too) but finally I realized that I needed a memory to store the data and even if no array type was available or params option another memory was necessary. Then I thought about (object to string -) serialization and how serialization can help to pass the items to the stored procedure.

So I built the following algorithm which deserializes ids from a string to a table (idstring represents the parameter, idtable is the result table).

 -- render a comma separated list of IDs into a table
 -- this targets on identity columns
 -- input string
 declare @idstring nvarchar(max) =

 -- output object
 declare @idtable Table(id int)
 -- object to work on
 declare @parsedID   int = 0

 -- iteration variable
 declare @i   int = 0
 -- sign to work on
 declare @c   nvarchar(10)
 -- walk over
 while(@i <= len(@idstring))
  -- query sign to work on
  set @c = substring(@idstring, @i, 1);

  if(@c = ',')
   insert into @idtable values (@parsedID)
   set @parsedID = 0;
  end else if(ISNUMERIC(@c) = 1) begin
   set @parsedID = @parsedID * 10 + (cast(@c as int));
  -- increment iteration variable to walk over next item
  set @i = @i + 1;
 if(@parsedID <> 0) begin
  insert into @idtable values (@parsedID)
 select * from @idtable

kind regards,

EDIT: found more or less my solution in a technet article (2002)

No comments: