由逗号分隔的类似数组的字符串执行存储过程 [英] Execute stored procedure with array-like string separated by comma

问题描述

可能重复：
  <一href="http://stackoverflow.com/questions/5426499/help-with-a-sql-search-query-using-a-comma-delimitted-parameter">Help使用一个SQL搜索查询逗号delimitted参数

我想写一个存储过程，执行一个选择上的表，需要键入 VARCHAR（最大）的一个输入变量。

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中的code会

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

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