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

Question

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

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

'Jack','Jane','Joe'

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

在SQL中，代码将是

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

现在我想在我的C＃应用​​程序中有一个变量，说出strNames并像这样填充它

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

并将此变量发送到SP并执行它。就像是

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

但是 如何告诉SQL Server运行这样的命令？

我需要做到这一点，我知道这是可能的，请给我提示。

Answer 1

首先，当您将单个名称传递给存储过程时，不需要使用单引号。

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

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

然后，在存储过程中，可以使用简单的文本函数和如下所示的临时表在逗号处分割字符串，并为字符串的每个部分输入临时表的条目：

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不包含任何行（未经过滤的结果），则列出所有这些人员：

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