Can a stored procedure have dynamic parameters to

问题:

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.

回答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.

回答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

回答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.

回答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

回答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.

回答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