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.

Rozier

are the id's generated by user input? – Syncopation

The user selects the records to search on with a checkbox but they don't have control over what the actual "Id" is. – Rozier

Then dynamic sql isn't a bad idea if it isn't subject to injection...but try the answer i posted instead to be safe. – Syncopation

If the values of the checkboxes aren't validated in some manner when they are returned to the server, then anyone can update the values of the checkboxes to be inject the value (assuming a web application here). If the values are coming from a winforms or something like wpf then you should be ok. – Photooffset

The user checks the checkboxs on winform app. Thanks for the pointers – Rozier

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.

Photooffset

Since your link is no longer working, I will share this one that does: jerrytech.blogspot.com/2008/04/… – Scavenge

Use the INNER JOIN not the IN ( clause if you use a table value parameter – Ted

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

Barner

Answer 3

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.

Syncopation

THis is possibly the best solution becase you never know just how much stinking data you are passing into that stored procedure. You can significantly improve performance using a temp table like that if the quantity is high. – Scavenge

Answer 4

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

Youngster

Answer 5

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.

Stoichiometric

You do not need dynamic SQL to parse a delimited list. See the page by Erland Sommarskog, as mentioned by Alex. – Kliment

Hey Remus, I disagree. I work on 2005, I tuck numbers into an image and parse them using a UDF: sommarskog.se/arrays-in-sql-2005.html – Barner

true, I reckon string processing is a viable alternative and I should have mention it. Coffee deprived post :) – Stoichiometric

Answer 6

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

Discreet