July 25, 2004

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 how I managed 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

But even that was a bit messy - the block that breaks off the next ID is used twice. 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 :)

Posted by dottie at 1:47 AM