Can a stored procedure have dynamic parameters to be used in an "IN" clause?

Question

I want to run a query like this:

 SELECT * FROM Studio WHERE Id IN (134, 144, 132, 138, 7432, 7543, 2566)

but the amount of Id's passed to the IN clause is only determined at runtime.

Do I have to use dynamic SQL or can this be done with a stored procedure?

UPDATE: If either option is available, which one is better?

Thanks.

Answer 1

Solution described here:

Arrays and Lists in SQL Server 2005

An SQL text by Erland Sommarskog, SQL Server MVP

http://www.sommarskog.se/arrays-in-sql-2005.html

Answer 2

Depending on your version of Sql Server, you can do this one of two different ways.

For Sql 2000/2005, you can use a parameter (type varchar) that has a delimited list of IDs. Create a UDF that would parse the varchar and return a table containing the items. Then make your IN clause go against the table (i.e. ...IN (Select ID FROM @ReturnTable)).

Here's an example of what the contents of the UDF would look like: http://pietschsoft.com/post/2006/02/03/T-SQL-Parse-a-delimited-string.aspx

For Sql 2008, you can do the same thing; however instead of passing in a varchar parameter you can just cut to the chase and pass in a Table parameter. The IN clause would still have a subquery but it would work all the same. Alternatively, once you have the table you can just do an Inner Join on it and circumvent the need for the IN clause.

EDIT: added UDF for parsing a delimited string link.

Answer 3

In SQL 2008 you can use a table valued parameter.

In SQL 2005 you must use dynamic SQL unless you want to pass the list as XML and use XML processing in the procedure to shred the XML back into a table variable.

Answer 4

declare a @temp table and split the values into it. then you could do

select * from Studio s inner join @temptable tb on s.ID=tb.ID

Answer 5

You can absolutely do this in a stored procedure.

create a temp table inside the stored procedure and insert the values split on the commas or any delimiter then do this

SELECT * FROM Studio WHERE Id IN (select id from temptable)

Then delete the table.

Answer 6

Here is a UDF that I've been using since MSSQL 2000. I found this somewhere - sorry, can't remember where.

Basically, you can do a join on the UDF, where the first param is the delimited string, and the second param is the delimiter.

SELECT t1.somecolumn FROM sometable t1 INNER JOIN dbo.Split(@delimitedVar, ',') t2 ON t1.ID = t2.Element

CREATE FUNCTION [dbo].[Split]
(
@vcDelimitedString varchar(max),
@vcDelimiter varchar(100)
)
RETURNS @tblArray TABLE
   (
    ElementID smallint IDENTITY(1,1), --Array index
    Element varchar(1000) --Array element contents
   )
AS
BEGIN
    DECLARE @siIndex smallint, @siStart smallint, @siDelSize smallint
    SET @siDelSize = LEN(@vcDelimiter)
    --loop through source string and add elements to destination table array
    WHILE LEN(@vcDelimitedString) > 0
    BEGIN
     SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
     IF @siIndex = 0
     BEGIN
      INSERT INTO @tblArray VALUES(@vcDelimitedString)
      BREAK
     END
     ELSE
     BEGIN
      INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
      SET @siStart = @siIndex + @siDelSize
      SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
     END
    END
    RETURN
END