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

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

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

5 个解决方案

#1

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

您可以将它作为一个逗号分隔的列表传入，然后使用一个split函数，并对结果进行联接。

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:

您可以在这里看到一些背景、其他选项和性能比较:

• http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx
• http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx
• http://sqlperformance.com/2012/07/t-sql-queries/split-strings
• http://sqlperformance.com/2012/07/t-sql-queries/split-strings
• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up

Of course, if you're using SQL Server 2008 or better, you may be able to pass these in much more efficiently using Table-Valued Parameters (TVPs). See here for a quick intro:

当然，如果您正在使用SQL Server 2008或更好的版本，您可以使用表值参数(TVPs)更有效地传递这些参数。这里有一个简短的介绍:

• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql
• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

#2

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 null or don't add them to cmd.Parameters at all. They will have their default value in the stored procedure

对于不希望传递的参数，要么将它们设置为null，要么不将它们添加到cmd。参数。它们将在存储过程中具有默认值

#3

SQLServer lets you pass TABLE parameter 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允许您将表参数传递到存储过程。因此，可以定义表类型，将类型LIST_OF_IDS创建为表(id int不是null主键)，修改过程以接受这种类型的变量(应该是只读的)。

#4

Have you considered using dictionary for 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

#5

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_split function 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