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

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

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)    ENDEND

稍后使用以下

SELECt

@temp

@temp

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