将动态数量的参数传递给存储过程 [英] Pass in Dynamic number of parameters to a stored procedure

问题描述

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

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

例如:select * from tbl where x=1 or x=2 or x=3 or x=4

是否可以在 .NET 和 SQL 中进行?我如何在 .NET 中创建动态参数(我想用循环来做)但是我如何在我的存储过程中声明它们?sql 有数组吗?

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?

请帮忙.

谢谢！

推荐答案

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

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.

表函数:

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

这篇关于将动态数量的参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！