Passing a variable number of parameters to a stored procedure

I came up against this problem any times. Say you want to retrieve the details of a few rows from a table by passing each of those variables into a stored procedure.

Traditionally you would have to have an array of ID's (or whatever you are basing your SELECT on) and you would loop through each value in your code, calling the stored procedure each time with all the rigmarole of opening a connection, reading the data, closing the connection, doing stuff with the data, then going back through the loop again.

Its a bit monolithic. No finesse.

It would be much better if you could just pass the stored procedure a list of ID's and have it return a recordset as normal.

Lets have an example table first:

Table name: AddressBook
ID	Name		Telephone	Email
1	John Boy	123456		jb@mail.com
2	Micky		98792837	mc@home.nl
:	:		:		:
etc....

you get the idea

Here's my first stab at it:

CREATE PROCEDURE DatabaseName.ProcName
  @Ids	VARCHAR(500);
AS
  CREATE TABLE #IDs ( ID INT );
  DECLARE @Comma 		INT;
  DECLARE @ID	INT;
BEGIN
/* now process the @Ids variable */
  SELECT @Comma = CHARINDEX( ',' , @Ids , 0 );
  IF @Comma = 0 BEGIN
    SELECT @ID = (@Ids);
  END

  IF @Comma <> 0 BEGIN
    SELECT @ID = LEFT( @Ids, @Comma - 1 );
    SELECT @Ids = RIGHT( @Ids, LEN(@Ids) - @Comma );
  END

  WHILE LEN(@Ids) > 0 BEGIN
    INSERT INTO #ConfigsID (@ID) VALUES (ConfigID);
    /* now process the @Ids variable */
    SELECT @Comma = CHARINDEX( ',' , @Ids , 0 );
    IF @Comma = 0 BEGIN
      SELECT @ID = (@Ids);
    END
    IF @Comma <> 0 BEGIN
      SELECT @ID = LEFT( @Ids, @Comma - 1 );
      SELECT @Ids = RIGHT( @Ids, LEN(@Ids) - @Comma );
    END
  END
  SELECT DISTINCT * FROM AddresBook WHERE ID IN (#IDs) ORDER BY Name;
END
GO

It works with SQL server 7, but is a bit messy - the block that breaks off the next ID is used twice. Taking advantage of SQL server 200, we can modularise that by creating a user defined function and readjusting the code, so:

CREATE FUNCTION splitString ( @String NVARCHAR(500) )
  RETURNS @Return  TABLE ( ID INT, List NVARCHAR(500))
AS
BEGIN
  DECLARE @Comma 	INT;
  DECLARE @ID INT;
  SELECT @Comma = CHARINDEX( ',' , @String , 0 );
  IF @Comma = 0 BEGIN
    SELECT @ID = (@String);
    SELECT @String = (NULL);
  END
  IF @Comma <> 0 BEGIN
    SELECT @ID = LEFT( @String, @Comma - 1 );
    SELECT @String = RIGHT( @String, LEN(@String) - @Comma );
  END
  INSERT @Return
    SELECT @ID, @String
  RETURN
END

CREATE FUNCTION splitList ( @List NVARCHAR(500) )
  RETURNS @Return  TABLE ( ID INT )
AS
BEGIN
  DECLARE @ID INT;
  DECLARE @Temp TABLE ( ID INT );
  SELECT @ID = ( SELECT ID FROM splitString( @List ) );
  SELECT @List = ( SELECT List FROM splitString( @List ) );
  WHILE LEN(@List) > 0 BEGIN
    INSERT @Temp SELECT ( @ID );
    SELECT @ID = ( SELECT ID FROM splitString( @List ) );
    SELECT @List = ( SELECT List FROM splitString( @List ) );
  END
  INSERT @Temp SELECT ( @ID );
  INSERT @Return
  SELECT * FROM @Temp
  RETURN
END
CREATE PROCEDURE Database.FunctionName
  @IDs VARCHAR(500)
AS
  CREATE TABLE #IDs ( ID INT )
BEGIN
  INSERT #IDs SELECT * FROM splitList( @IDs );
  SELECT DISTINCT * FROM AddressBook WHERE ID IN (SELECT * FROM #IDs) ORDER BY Name;
END
GO

Your code is cleaner as you can use your stored procedure like a normal stored procedure and just pass it a comma delimeted list of ID's to retrieve a recordset. Cant say it will do too much for your SQL server efficiency though - I have never had to deal with huge overheads so I've no idea what effect this will have on your performance.

The code side of things should be much more efficient as the connection needs only be made once. The SQL server is less efficient though with all that string parsing. There should be a sweet spot though. I leave it up to better men than I to find out where it is :)