将动态数量的参数传递给存储过程

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

我的.NET应用程序中有一个函数，需要搜索未知数量的参数。

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

例如：select * from tbl，其中x = 1或x = 2或x = 3或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?

可以在.NEt和SQL中做吗？我如何在.NET中创建动态参数（我想用循环来做）但是我如何在我的存储过程中声明它们？ sql有数组吗？

please help.

请帮忙。

thank you!

谢谢！

3 个解决方案

#1

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.

您可以传入逗号分隔列表，使用表函数将其拆分为表，然后使用IN子句。这篇文章就是这样做的。

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,',')
                   )

#2

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

您可能希望查看表值参数（SQL Server 2008及更高版本）：

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

#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

尝试传入XML列表作为参数，然后您可以使用游标或类似的东西处理XML列表中的项目

#1