如何向SQL Server存储过程传递带单引号的多ID字符串？

问题场景 #

我尝试向存储过程传递一个包含多个带单引号ID的字符串，用于存储过程中的IN子句，但测试时设置字符串报错，请求解决传参方法。

测试SQL代码：

DECLARE @str1 AS  NVARCHAR(max)
SET @str1 = '3229622','4183229','3257553','3003673','3358312','0682773','4069249','0854620','4667379','0013862','1319623','3220826','3405633','0797654','3240120'

--print @str1
EXEC [GetMemberInfoAndMemberSubscriptions] @str1

报错信息：

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ','

对应的存储过程代码：

CREATE OR ALTER PROCEDURE [dbo].[GetMemberInfoAndMemberSubscriptions] 
    (@ip_master_customer_ids AS NVARCHAR(max))
AS
BEGIN
    SELECT 
        [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status  
    FROM 
        CUSTOMER 
    WHERE 
        [MASTER_CUSTOMER_ID] IN (@ip_master_customer_ids)
END

调用存储过程的C#代码：

string MemberNumbers = "'3229622','4183229','3257553','3003673','3358312','0682773','4069249','0854620','4667379','0013862','1319623','3220826','3405633','0797654','3240120'";

已知@Nick建议使用表值参数，对应代码：

CREATE TYPE StringList AS TABLE (Id nvarchar(50));

请问除了该方案外，还有哪些可行的解决办法？

替代解决方案 #

1. 使用动态SQL拼接 #

修改存储过程，将传入的字符串拼接成完整SQL语句执行，但需注意SQL注入风险，建议添加输入验证：

CREATE OR ALTER PROCEDURE [dbo].[GetMemberInfoAndMemberSubscriptions] 
    (@ip_master_customer_ids AS NVARCHAR(max))
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = N'SELECT [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status  
                 FROM CUSTOMER 
                 WHERE [MASTER_CUSTOMER_ID] IN (' + @ip_master_customer_ids + N')'
    -- 验证输入仅包含数字、单引号和逗号，降低注入风险
    IF PATINDEX('%[^0-9'','']%', @ip_master_customer_ids) = 0
        EXEC sp_executesql @sql
    ELSE
        RAISERROR('非法输入', 16, 1)
END

调用时传入带单引号的ID字符串，与现有C#代码定义的MemberNumbers格式一致即可。

2. 自定义字符串拆分函数 #

创建拆分函数将逗号分隔的ID字符串转为表结构，再用于IN子句：
首先创建拆分函数：

CREATE FUNCTION dbo.SplitString
(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(5)
)
RETURNS @OutputTable TABLE (Id NVARCHAR(50))
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT
    SET @StartIndex = 1
    IF SUBSTRING(@InputString, LEN(@InputString) - 1, 1) <> @Delimiter
        SET @InputString = @InputString + @Delimiter
    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString)
        INSERT INTO @OutputTable(Id)
        VALUES(SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex))
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString))
    END
    RETURN
END

修改存储过程：

CREATE OR ALTER PROCEDURE [dbo].[GetMemberInfoAndMemberSubscriptions] 
    (@ip_master_customer_ids AS NVARCHAR(max))
AS
BEGIN
    SELECT 
        [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status  
    FROM 
        CUSTOMER 
    WHERE 
        [MASTER_CUSTOMER_ID] IN (SELECT Id FROM dbo.SplitString(@ip_master_customer_ids, ','))
END

注意：调用时传入的字符串无需带单引号，C#代码需调整为：

string MemberNumbers = "3229622,4183229,3257553,...";

3. 使用内置字符串拆分函数（SQL Server 2016+） #

若使用SQL Server 2016及以上版本，可直接用内置的STRING_SPLIT函数，无需自定义：
修改存储过程：

CREATE OR ALTER PROCEDURE [dbo].[GetMemberInfoAndMemberSubscriptions] 
    (@ip_master_customer_ids AS NVARCHAR(max))
AS
BEGIN
    SELECT 
        [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status  
    FROM 
        CUSTOMER 
    WHERE 
        [MASTER_CUSTOMER_ID] IN (SELECT value FROM STRING_SPLIT(@ip_master_customer_ids, ','))
END

调用时传入无单引号的逗号分隔字符串，同步调整C#代码格式。

4. 传递多个固定参数（适用于ID数量固定场景） #

如果ID数量固定，可直接在存储过程中定义多个参数：

CREATE OR ALTER PROCEDURE [dbo].[GetMemberInfoAndMemberSubscriptions] 
    (@id1 NVARCHAR(50), @id2 NVARCHAR(50), @id3 NVARCHAR(50), ...)
AS
BEGIN
    SELECT 
        [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status  
    FROM 
        CUSTOMER 
    WHERE 
        [MASTER_CUSTOMER_ID] IN (@id1, @id2, @id3, ...)
END

此方法灵活性差，仅适用于ID数量明确且固定的场景。

内容的提问来源于stack exchange，提问作者James123