Pass in Dynamic number of parameters to a stored p

Question

I have a function in my .NET application, that needs to do a search of an unknown number of parameters.

for example: select * from tbl where x=1 or x=2 or x=3 or x=4

is it possible to do in .NEt and SQL? how do i go about creating dynamic parameters in .NET (I was thinking doing it with a loop) but then how do i declare them in my stored procedure? does sql have arrays?

please help.

thank you!

Answer 1

You might want to look at table-valued parameters (SQL Server 2008 and up):

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Answer 2

You can pass in a comma seperated list, use a table function to split that out into a table and then use an IN clause. This article goes over doing that.

table function:

CREATE FUNCTION dbo.funcListToTableInt(@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Value INT
  )
AS
BEGIN
    --Declare helper to identify the position of the delim
    DECLARE @DelimPosition INT

    --Prime the loop, with an initial check for the delim
    SET @DelimPosition = CHARINDEX(@delim, @list)

    --Loop through, until we no longer find the delimiter
    WHILE @DelimPosition > 0
    BEGIN
        --Add the item to the table
        INSERT INTO @listTable(Value)
            VALUES(CAST(RTRIM(LEFT(@list, @DelimPosition - 1)) AS INT))

        --Remove the entry from the List
        SET @list = right(@list, len(@list) - @DelimPosition)

        --Perform position comparison
        SET @DelimPosition = CHARINDEX(@delim, @list)
    END

    --If we still have an entry, add it to the list
    IF len(@list) > 0
        insert into @listTable(Value)
        values(CAST(RTRIM(@list) AS INT))

  RETURN
END
GO

Then your stored proc can do this:

SELECT *
FROM tbl 
WHERE id IN (
            SELECT Value
            FROM funcListToTableInt(@ids,',')
                   )

Answer 3

Try passing in an XML list as the parameter, then you can work through the items in the XML list with a cursor or something similar