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

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 2

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