SQL 如何在存储过程中将列表作为参数传递？

Question

提问by Matthew Sontum

Looking to pass a list of User IDs to return a list names. I have a plan to handle the outputed names (with a COALESCE something or other) but trying to find the best way to pass in the list of user IDs. The guts of my sproc will look something like this:

希望传递用户 ID 列表以返回列表名称。我计划处理输出的名称（使用 COALESCE 或其他），但试图找到传递用户 ID 列表的最佳方式。我的 sproc 的内容看起来像这样：

create procedure [dbo].[get_user_names]
@user_id_list, --which would equal a list of incoming ID numbers like (5,44,72,81,126)
@username varchar (30) output
as
select last_name+', '+first_name 
from user_mstr
where user_id in @user_id_list

Passing the values for @user_id_list is my main concern here.

传递@user_id_list 的值是我在这里的主要关注点。

Answer 1

回答by Matthew Sontum

The preferred method for passing an array of values to a stored procedure in SQL server is to use table valued parameters.

将值数组传递给 SQL Server 中的存储过程的首选方法是使用表值参数。

First you define the type like this:

首先你定义类型是这样的：

CREATE TYPE UserList AS TABLE ( UserID INT );

Then you use that type in the stored procedure:

然后在存储过程中使用该类型：

create procedure [dbo].[get_user_names]
@user_id_list UserList READONLY,
@username varchar (30) output
as
select last_name+', '+first_name 
from user_mstr
where user_id in (SELECT UserID FROM @user_id_list)

So before you call that stored procedure, you fill a table variable:

因此，在调用该存储过程之前，您需要填充一个表变量：

DECLARE @UL UserList;
INSERT @UL VALUES (5),(44),(72),(81),(126)

And finally call the SP:

最后调用 SP：

EXEC dbo.get_user_names @UL, @username OUTPUT;

Answer 2

回答by Ambrose Little

As far as I can tell, there are three main contenders: Table-Valued Parameters, delimited list string, and JSON string.

据我所知，有三个主要竞争者：表值参数、分隔列表字符串和 JSON 字符串。

Since 2016, you can use the built-in STRING_SPLIT if you want the delimited route: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

自 2016 年以来，如果您想要分隔路线，您可以使用内置的 STRING_SPLIT：https: //docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

That would probably be the easiest/most straightforward/simple approach.

这可能是最简单/最直接/最简单的方法。

Also since 2016, JSON can be passed as a nvarchar and used with OPENJSON: https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

同样自 2016 年以来，JSON 可以作为 nvarchar 传递并与 OPENJSON 一起使用：https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

That's probably best if you have a more structured data set to pass that may be significantly variable in its schema.

如果您有一个更结构化的数据集要传递，并且其架构中可能会有很大的变化，那这可能是最好的。

TVPs, it seems, used to be the canonical way to pass more structured parameters, and they are still good if you need that structure, explicitness, and basic value/type checking. They can be a little more cumbersome on the consumer side, though. If you don't have 2016+, this is probably the default/best option.

TVP 似乎曾经是传递更多结构化参数的规范方式，如果您需要这种结构、明确性和基本的值/类型检查，它们仍然很好。不过，它们在消费者方面可能会更麻烦一些。如果您没有 2016+，这可能是默认/最佳选项。

I think it's a trade off between any of these concrete considerations as well as your preference for being explicit about the structure of your params, meaning even if you have 2016+, you may prefer to explicitly state the type/schema of the parameter rather than pass a string and parse it somehow.

我认为这是在任何这些具体考虑因素以及您对明确参数结构的偏好之间的权衡，这意味着即使您有 2016+，您可能更愿意明确说明参数的类型/架构而不是传递一个字符串并以某种方式解析它。

Answer 3

回答by BehrouzMoslem

I solved this problem through the following:

我通过以下方式解决了这个问题：

In C # I built a String variable.

在 C# 中，我构建了一个 String 变量。

string userId="";

字符串 userId="";

I put my list's item in this variable. I separated the ','.

我将我的列表项放在这个变量中。我分隔了'，'。

for example: in C#
userId= "5,44,72,81,126";

例如：在 C# 中
userId= "5,44,72,81,126";

and Send to SQL-Server

并发送到 SQL-Server

 SqlParameter param = cmd.Parameters.AddWithValue("@user_id_list",userId);

 SqlParameter param = cmd.Parameters.AddWithValue("@user_id_list",userId);

I Create Separated Function in SQL-server For Convert my Received List (that it's type is NVARCHAR(Max)) to Table.

我在 SQL-server 中创建了分隔函数，用于将我收到的列表（它的类型是NVARCHAR(Max)）转换为表。

CREATE FUNCTION dbo.SplitInts  
(  
   @List      VARCHAR(MAX),  
   @Delimiter VARCHAR(255)  
)  
RETURNS TABLE  
AS  
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM  
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')  
        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 FUNCTION dbo.SplitInts  
(  
   @List      VARCHAR(MAX),  
   @Delimiter VARCHAR(255)  
)  
RETURNS TABLE  
AS  
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM  
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')  
        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  
  );

In the main Store Procedure, using the command below, I use the entry list.

在主存储过程中，使用下面的命令，我使用条目列表。

SELECT user_id = Item FROM dbo.SplitInts(@user_id_list, ',');

Answer 4

回答by Peter Henry

Azure DB, Azure Data WH and from SQL Server 2016, you can use STRING_SPLIT to achieve a similar result to what was described by @sparrow.

从 Azure DB、Azure Data WH 和 SQL Server 2016 开始，您可以使用 STRING_SPLIT 来实现与 @sparrow 所描述的类似的结果。

Recycling code from @sparrow

从@sparrow 回收代码

WHERE user_id IN (SELECT value FROM STRING_SPLIT( @user_id_list, ',')

Simple and effective way of accepting a list of values into a Stored Procedure

将值列表接受到存储过程中的简单有效的方法

Answer 5

回答by Sparrow

You can try this:

你可以试试这个：

create procedure [dbo].[get_user_names]
    @user_id_list varchar(2000), -- You can use any max length

    @username varchar (30) output
    as
    select last_name+', '+first_name 
    from user_mstr
    where user_id in (Select ID from dbo.SplitString( @user_id_list, ',') )

And here is the user defined function for SplitString:

这是 SplitString 的用户定义函数：

Create FUNCTION [dbo].[SplitString]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

Answer 6

回答by Skippy

Maybe you could use:

也许你可以使用：

select last_name+', '+first_name 
from user_mstr
where ',' + @user_id_list + ',' like '%,' + convert(nvarchar, user_id) + ',%'

Answer 7

回答by Chris Halcrow

You can use this simple 'inline' method to construct a string_list_type parameter (works in SQL Server 2014):

您可以使用这个简单的“内联”方法来构造 string_list_type 参数（适用于 SQL Server 2014）：

declare @p1 dbo.string_list_type
insert into @p1 values(N'myFirstString')
insert into @p1 values(N'mySecondString')

Example use when executing a stored proc:

执行存储过程时的示例使用：

exec MyStoredProc @MyParam=@p1

SQL 如何在存储过程中将列表作为参数传递？

提问by Matthew Sontum

回答by Matthew Sontum

回答by Ambrose Little

回答by BehrouzMoslem

回答by Peter Henry

回答by Sparrow

回答by Skippy

回答by Chris Halcrow

相关推荐

最近更新

标签

SQL 如何在存储过程中将列表作为参数传递？

提问by Matthew Sontum

回答by Matthew Sontum

回答by Ambrose Little

回答by BehrouzMoslem

回答by Peter Henry

回答by Sparrow

回答by Skippy

回答by Chris Halcrow

相关推荐

SQL 如何在创建之前检查存储过程是否存在

如何在 SQL Server 中声明数组变量？

如何在 SQL 中设置布尔值

SQL sql查询返回两个表之间的差异

相关推荐

最近更新

标签