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

发布于 2024-12-05 05:21:52 字数 179 浏览 1 评论 0原文

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

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

任何人都可以帮助我如何传递这些可变数量的参数存储过程中的参数？谢谢

原文

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.

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

策马西风 2024-12-12 05:21:52

您可以将其作为逗号分隔的列表传递，然后使用 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
   );

现在是您的存储过程：

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

然后调用它：

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

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

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

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:

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

回复收藏 0 原文

浪荡不羁 2024-12-12 05:21:52

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

回复收藏 0 原文

请叫√我孤独 2024-12-12 05:21:52

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

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

对于您不想传递的参数，请将它们设置为 null 或根本不将它们添加到 cmd.Parameters 中。它们将在存储过程中具有默认值

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

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

回复收藏 0 原文

我不吻晚风 2024-12-12 05:21:52

您是否考虑过使用字典来实现此目的？
它将允许您将任意数量的参数作为键值对传递。
然后你只需要浏览字典并将这些参数添加到 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
}

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

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

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.

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

回复收藏 0 原文

痴者 2024-12-12 05:21:52

下面是一个基于 , 作为分隔符分割字符串的代码片段。您甚至可以参数化逗号。它对于还没有 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

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:

  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

回复收藏 0 原文