如何在存储过程的@GDNNO参数中传入多个值？

针对你提供的存储过程dbo.xspSHEGONPrintQuery4，要让@GDNNO参数支持传入多个值，这里提供几种实用方案：

方案1：使用STRING_SPLIT函数（SQL Server 2016及以上版本适用） #

SQL Server 2016及以后版本自带STRING_SPLIT函数，可直接拆分逗号分隔的字符串。修改后的存储过程如下：

ALTER PROCEDURE dbo.xspSHEGONPrintQuery4
    @GDNNO varchar(4000) = ''
AS
BEGIN
    SELECT
        a.ID, a.[To], a.FlowCode, a.TranNum, a.Status,
        ART.ARTICLECODE, StockGDNID, DetailID, SubDocCode, ArticleID,
        ColorCode, ColorName, SizeCode, SizeName,
        DispatchedUnits  * (CASE WHEN u.value IS NULL THEN FreezeStoringUOM ELSE u.value END) AS DispatchedUnits
    FROM
        xtstockgdn a (nolock)
    JOIN
        xtstockgdndetail b (nolock) ON a.id = b.stockgdnid
    JOIN 
        XTARTICLE ART (nolock) ON ART.ID = B.ARTICLEID
    LEFT JOIN
        xtUOMConversion u (nolock) ON u.FromUOM = art.StoringUOM
                                   AND u.ToUOM = art.consuom
    WHERE 
        -- 拆分传入的逗号分隔字符串，匹配多个ID
        a.ID IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@GDNNO, ','))
        AND FlowCode = 'POO_RET[E-]'
END

调用示例：EXEC dbo.xspSHEGONPrintQuery4 @GDNNO = 'GDN001,GDN002,GDN003'

方案2：使用表值参数（更规范，适合复杂场景） #

如果需要更严谨的参数传递，推荐使用表值参数，步骤如下：

1. 先创建表值类型：

CREATE TYPE GDNNOList AS TABLE (GDNNO VARCHAR(4000));
GO

2. 修改存储过程，将参数改为表值类型：

ALTER PROCEDURE dbo.xspSHEGONPrintQuery4
    @GDNNO GDNNOList READONLY
AS
BEGIN
    SELECT
        a.ID, a.[To], a.FlowCode, a.TranNum, a.Status,
        ART.ARTICLECODE, StockGDNID, DetailID, SubDocCode, ArticleID,
        ColorCode, ColorName, SizeCode, SizeName,
        DispatchedUnits  * (CASE WHEN u.value IS NULL THEN FreezeStoringUOM ELSE u.value END) AS DispatchedUnits
    FROM
        xtstockgdn a (nolock)
    JOIN
        xtstockgdndetail b (nolock) ON a.id = b.stockgdnid
    JOIN 
        XTARTICLE ART (nolock) ON ART.ID = B.ARTICLEID
    LEFT JOIN
        xtUOMConversion u (nolock) ON u.FromUOM = art.StoringUOM
                                   AND u.ToUOM = art.consuom
    WHERE 
        a.ID IN (SELECT GDNNO FROM @GDNNO)
        AND FlowCode = 'POO_RET[E-]'
END

调用示例：

DECLARE @GDNList GDNNOList;
INSERT INTO @GDNList VALUES ('GDN001'), ('GDN002'), ('GDN003');
EXEC dbo.xspSHEGONPrintQuery4 @GDNNO = @GDNList;

方案3：自定义字符串拆分函数（SQL Server 2016以下版本适用） #

如果你的SQL Server版本低于2016，可以自定义一个字符串拆分函数：

CREATE FUNCTION dbo.SplitString
(
    @InputString VARCHAR(MAX),
    @Delimiter VARCHAR(10)
)
RETURNS @OutputTable TABLE (Value VARCHAR(4000))
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT;
    SET @StartIndex = 1;
    IF SUBSTRING(@InputString, LEN(@InputString) - 1, LEN(@InputString)) <> @Delimiter
    BEGIN
        SET @InputString = @InputString + @Delimiter;
    END

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString);
        INSERT INTO @OutputTable(Value)
        VALUES(LTRIM(RTRIM(SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex))));
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString));
    END

    RETURN;
END
GO

然后修改存储过程的WHERE条件：

WHERE 
    a.ID IN (SELECT Value FROM dbo.SplitString(@GDNNO, ','))
    AND FlowCode = 'POO_RET[E-]'

注意事项 #

• 如果传入的多值数量较多，建议把@GDNNO的类型从varchar(4000)改为varchar(max)避免截断；
• NOLOCK提示要谨慎使用，它可能导致脏读、不可重复读等问题，确认业务场景允许再保留；
• 传入逗号分隔值时，尽量避免值前后有空格，或者在拆分时用LTRIM(RTRIM)处理掉空格。

内容的提问来源于stack exchange，提问作者Ankur Singh Bhandari