使用逗号分隔的类数组字符串执行存储过程

Execute stored procedure with array-like string separated by comma(使用逗号分隔的类数组字符串执行存储过程)

问题描述

可能重复:
帮助使用逗号分隔的 sql 搜索查询参数

我想编写一个对表执行选择的存储过程，并且需要一个 varchar(max) 类型的输入变量.

I want to write a stored procedure that performs a select on a table and need one input variable of type varchar(max).

我想发送一堆由 , 分隔的值作为输入参数，例如

I'd like to send a bunch of values separated by , as the input parameter, e.g.

'Jack','Jane','Joe'

然后获取包含这些名称之一的行.

and then get the rows that contain one of these names.

在 SQL 中代码是

In SQL the code would be

Select * from Personnel where Name in ('Jack','Joe','Jane');  

现在我想在我的 C# 应用程序中有一个变量，比如 strNames 并像这样填充它

Now I want to have a variable in my C# app, say strNames and fill it like

string strNames = "'Jack','Joe','Jane'";

并将这个变量发送到 SP 并执行它.像

and send this variable to the SP and execute it. Something like

Select * from Personnel where Name in (''Jack','Joe','Jane'') -- this is wrong

但是我怎样才能告诉 SQL Server 运行这样的命令呢?

我需要做到这一点，我知道这是可能的，请给我线索.

I need to make this happen and I know it's possible, please give me the clue.

推荐答案

首先，单个名称在传递给存储过程时不需要加引号.

First of all, the single names don't need to be quoted when you pass them to the stored procedure.

using (SqlCommand cmd = new SqlCommand("MyStoredProc", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@longFilter", "Jack,Jill,Joe");

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        ...
    }
}

然后，在存储过程中，您可以使用简单的文本函数和临时表，如下所示，以逗号分隔字符串，并为字符串的每个部分在临时表中添加一个条目:

Then, in the stored procedure, you can use simple text functions and a temporary table as follows to split up the string at the commas and an an entry to the temporary table for each part of the string:

DECLARE @temp AS TABLE (Name NVARCHAR(255))

IF ISNULL(@longFilter, '') <> ''
BEGIN
    DECLARE @s NVARCHAR(max)
    WHILE LEN(@longFilter) > 0
    BEGIN
        IF CHARINDEX(',', @longFilter) > 0
        BEGIN
            SET @s = LTRIM(RTRIM(SUBSTRING(@longFilter, 1, CHARINDEX(',', @longFilter) - 1)))
            SET @longFilter = SUBSTRING(@longFilter, CHARINDEX(',', @longFilter) + 1, LEN(@longFilter))
        END ELSE
        BEGIN
            SET @s = LTRIM(RTRIM(@longFilter))
            SET @longFilter= ''
        END

        -- This was missing until 20140522
        INSERT INTO @temp (Name) VALUES (@s)
    END
END

稍后使用以下 SELECT 来获取姓名在 @temp 中的所有人的列表，或者如果 @temp 不包含任何行(未过滤的结果):

Later use the following SELECT to get a list of all people the name of which is in @temp or all of them if @temp doesn't contain any rows (unfiltered result):

SELECT * FROM Personnel WHERE Name IN (SELECT Name FROM @temp) OR (SELECT COUNT(*) FROM @temp) = 0

这篇关于使用逗号分隔的类数组字符串执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！