SQL 具有可变数量参数的存储过程

Stored procedure with variable number of parameters

提问by CPDS

I have stored procedure where I have to pass parameters, But the problem is I am not sure how many parameters is going to come it can be 1, in next run it can be 5.

我有存储过程，我必须在其中传递参数，但问题是我不确定将要传入多少个参数，它可以是 1，下次运行时它可以是 5。

cmd.Parameters.Add(new SqlParameter("@id", id)

Can anyone help how can I pass these variable number of parameters in stored procedure? Thanks

谁能帮助我如何在存储过程中传递这些可变数量的参数？谢谢

回答by Aaron Bertrand

You could pass it in as a comma-separated list, then use a split function, and join against the results.

您可以将它作为逗号分隔的列表传入，然后使用拆分函数，并根据结果进行连接。

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN 
   (
       SELECT Item = CONVERT(INT, Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'INT')
           FROM
           (
               SELECT [XML] = CONVERT(XML, '<i>' 
                    + REPLACE(@List, @Delimiter, '</i><i>') 
                    + '</i>').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );

Now your stored procedure:

现在你的存储过程：

CREATE PROCEDURE dbo.doStuff
    @List VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cols FROM dbo.table AS t
        INNER JOIN dbo.SplitInts(@List, ',') AS list
        ON t.ID = list.Item;
END
GO

Then to call it:

然后调用它：

EXEC dbo.doStuff @List = '1, 2, 3, ...';

You can see some background, other options, and performance comparisons here:

您可以在此处查看一些背景、其他选项和性能比较：

• Split strings the right way – or the next best way
• Splitting Strings : A Follow-Up
• Splitting Strings : Now with less T-SQL
• Comparing string splitting / concatenation methods
• Processing a list of integers : my approach
• Splitting a list of integers : another roundup
• More on splitting lists : custom delimiters, preventing duplicates, and maintaining order
• Removing Duplicates from Strings in SQL Server

• 以正确的方式拆分字符串 - 或下一个最佳方式
• 拆分字符串：后续
• 拆分字符串：现在使用更少的 T-SQL
• 比较字符串拆分/连接方法
• 处理整数列表：我的方法
• 拆分整数列表：另一个综述
• 有关拆分列表的更多信息：自定义分隔符、防止重复和维护顺序
• 从 SQL Server 中的字符串中删除重复项

On SQL Server 2016 or above, though, you should look at STRING_SPLIT()and STRING_AGG():

但是，在 SQL Server 2016 或更高版本上，您应该查看STRING_SPLIT()和STRING_AGG()：

• Performance Surprises and Assumptions : STRING_SPLIT()
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #2
• SQL Server v.Next : STRING_AGG() performance
• Solve old problems with SQL Server's new STRING_AGG and STRING_SPLIT functions

• 性能惊喜和假设：STRING_SPLIT()
• SQL Server 2016 中的 STRING_SPLIT()：跟进 #1
• SQL Server 2016 中的 STRING_SPLIT()：跟进 #2
• SQL Server v.Next：STRING_AGG() 性能
• 解决 SQL Server 新增 STRING_AGG 和 STRING_SPLIT 函数的老问题

回答by Andomar

Stored procedures support optional parameters. Like C# 4, you can specify a default value using =. For example:

存储过程支持可选参数。与 C# 4 一样，您可以使用=. 例如：

create procedure dbo.doStuff(
     @stuffId int = null, 
     @stuffSubId int = null, 
     ...)
as
...

For parameters you don't want to pass, either set them to nullor don't add them to cmd.Parametersat all. They will have their default value in the stored procedure

对于不想传递的参数，要么将它们设置为，要么null根本不添加它们cmd.Parameters。它们将在存储过程中使用默认值

回答by a1ex07

SQLServer lets you pass TABLEparameter to the stored procedure. So you can define table type, CREATE TYPE LIST_OF_IDS AS TABLE (id int not null primary key), alter your procedure to accept a variable of this type (it should be readonly).

SQLServer 允许您将TABLE参数传递给存储过程。所以你可以定义表类型，CREATE TYPE LIST_OF_IDS AS TABLE (id int not null primary key)改变你的过程以接受这种类型的变量（它应该是只读的）。

回答by Li0liQ

Have you considered using dictionaryfor that purpose? It will allow you to pass any number of parameters as key-value pairs. Then you'll need just to go through the dictionary and add those parameters to cmd.

您是否考虑过为此目的使用字典？它将允许您将任意数量的参数作为键值对传递。然后你只需要浏览字典并将这些参数添加到 cmd。

void DoStuff(Dictionary<string, object> parameters)
{
    // some code
    foreach(var param in parameters)
    {
        cmd.Parameters.Add(new SqlParameter(param.Key, param.Value);
    }
    // some code
}

In stored procedure itself you'll need to specify default values for the parameters.

在存储过程本身中，您需要为参数指定默认值。

CREATE PROCEDURE DoStuff(
     @id INT = NULL,
     @value INT = NULL,
     -- the list of parameters with their default values goes here
     )
AS
-- procedure body

回答by Tamas Rev

Here is a code snippet that splits a string based on ,as delimiter. You can even parametrize the comma. It's useful on systems that don't have the String_splitfunction yet:

这是一个基于,分隔符分割字符串的代码片段。您甚至可以参数化逗号。在还没有该String_split功能的系统上很有用：

  DECLARE @startindex INT
  DECLARE @commaindex INT
  DECLARE @paramAsString VARCHAR(MAX) -- this represents the input param
  DECLARE @param VARCHAR (1024)
  DECLARE @paramsTable TABLE(param VARCHAR(1024) NOT NULL) -- the splitted params come here

  SET @startindex = 1
  WHILE @startindex < LEN(@paramAsString)
  BEGIN
    SET @commaindex = CHARINDEX(',', @paramAsString, @startindex)

    IF @commaindex = 0
    BEGIN
      SET @param = SUBSTRING(@paramAsString, @startindex, LEN(@paramAsString))
      SET @startindex = LEN(@settlementEntities)
    END
    ELSE
    BEGIN
      SET @param = SUBSTRING(@paramAsString, @startindex, (@commaindex - @startindex))
      SET @startindex = @commaindex + 1
    END
    IF @se IS NOT NULL AND 0 < LEN(RTRIM(LTRIM(@param)))
    BEGIN
      SET @param = RTRIM(LTRIM(@param))
      INSERT INTO @paramsTable (param) VALUES (@param)
    END
  END

回答by Rainhider

if you have Sql Server 2008 or later, you can use a table valued parameter...

如果您有 Sql Server 2008 或更高版本，则可以使用表值参数...

https://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

https://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/