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

Hi,

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) =
  '1,2,3,4,5,6,7,8,9,10,11,12,9999,13,752268687,100,56,322223,5'

 -- 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))
 begin
  -- query sign to work on
  set @c = substring(@idstring, @i, 1);

  if(@c = ',')
  begin
   insert into @idtable values (@parsedID)
   set @parsedID = 0;
  end else if(ISNUMERIC(@c) = 1) begin
   set @parsedID = @parsedID * 10 + (cast(@c as int));
  end;

  -- increment iteration variable to walk over next item
  set @i = @i + 1;
 end;

 if(@parsedID <> 0) begin
  insert into @idtable values (@parsedID)
 end;

 select * from @idtable



kind regards,
Daniel

EDIT: found more or less my solution in a technet article (2002) https://technet.microsoft.com/en-us/library/aa496058(v=sql.80).aspx