If I need to pass to pass a long list of ID's to a stored procedure, what is the
best way to do it? Right now, I have a parameter defined as varchar(8000),
which is a delimited list. I then have to loop through this string and operate
on each extracted ID, or I can use EXEC() and the IN clause to create a
temporary table, which I can then use with a JOIN or to create a cursor, sort of
like this:

CREATE PROCEDURE test
@id_list varchar(8000) /* comma-delimited list of id's */
AS
SET @strSql = 'SELECT * INTO #tmp FROM table1 WHERE table1.id IN (' + @id_list +
')'
EXEC (@strSql)
/* now do whatever with #tmp */
DROP TABLE #tmp


Is there a more elegant way? Please note that I am using SQL Server 7.0.

-- Matthew Solnit