向存储过程传多值遇报错：子查询返回多值的原因与解决问询

问题背景 #

原本创建了一个接收多值参数的存储过程，通过STRING_SPLIT拆分@ShiftTime参数实现IN查询，运行正常。为了实现@ShiftTime为null时返回所有符合其他条件的结果，修改了WHERE子句的条件，随后触发错误。

原存储过程代码 #

CREATE PROCEDURE [dbo].[RepCharpenteProductionByShiftTime]
    @FirstDate DATE,
    @LastDate DATE,
    @ShiftTime NVARCHAR(100)
AS
    SELECT 
        proinfo.ProjectN AS 'BS',
        cust.Name AS 'Client',
        proinfo.ProjectName AS 'Projet',
        SUM(ProShip.[Weight] * [Quantity]) AS 'Poids Total'
    FROM 
        [dbo].[DailyWeldingPaintProduction] daiPaiPro
    INNER JOIN
        ProjectShipping ProShip ON ProShip.id = [FK_idPartShip]
    INNER JOIN
        ProjectInfo proinfo ON proinfo.id = ProShip.IdProject
    INNER JOIN 
        Customer cust ON cust.ID = proinfo.FK_Customer
    LEFT JOIN
        PreviousWeight prev ON prev.FK_Project = proinfo.id
    WHERE 
        FK_idNextProcess = 13 
        AND CONVERT(DATE, [CreationDate]) BETWEEN @FirstDate AND @LastDate 
        AND FK_ShiftTime IN (SELECT value 
                             FROM STRING_SPLIT(@ShiftTime, ','))
    GROUP BY
        proinfo.ProjectN, cust.Name, proinfo.ProjectName,
        PreviousWeight, proinfo.Weight
    ORDER BY
        proinfo.ProjectN

修改后的WHERE条件 #

FK_ShiftTime IN (IIF(@ShiftTime IS NULL, daiPaiPro.FK_ShiftTime,(SELECT value FROM STRING_SPLIT(@ShiftTime, ','))))

错误提示 #

Msg 512, Level 16, State 1, Procedure dbo.RepCharpenteProductionByShiftTime, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

错误原因 #

IIF是标量函数，要求两个分支都返回单一值，但你的用法存在两个问题：

1. 当@ShiftTime不为null时，STRING_SPLIT返回的是多行结果，作为IIF的第二个分支，属于子查询返回多行，不符合标量函数的要求。
2. 当@ShiftTime为null时，daiPaiPro.FK_ShiftTime是表中的字段，每行对应不同的值，同样不是单一值，也不符合IIF的使用场景。
   这两种情况都会触发“子查询返回多值”的错误。

解决方法 #

方式一：逻辑判断拆分（推荐） #

直接通过OR逻辑处理null的情况，逻辑清晰且性能更优：

WHERE 
    FK_idNextProcess = 13 
    AND CONVERT(DATE, [CreationDate]) BETWEEN @FirstDate AND @LastDate 
    AND (@ShiftTime IS NULL OR FK_ShiftTime IN (SELECT value FROM STRING_SPLIT(@ShiftTime, ',')))

• 当@ShiftTime为null时，@ShiftTime IS NULL为true，整个OR条件成立，会跳过后面的IN判断，返回所有符合其他条件的数据。
• 当@ShiftTime不为null时，执行STRING_SPLIT拆分后的IN查询。

方式二：构造兼容的子查询 #

如果坚持用IN子查询的方式，可以通过UNION ALL构造包含所有可能值的集合：

WHERE 
    FK_idNextProcess = 13 
    AND CONVERT(DATE, [CreationDate]) BETWEEN @FirstDate AND @LastDate 
    AND FK_ShiftTime IN (
        -- 当参数为null时，返回当前表所有FK_ShiftTime值；否则返回拆分后的参数值
        SELECT DISTINCT FK_ShiftTime FROM [dbo].[DailyWeldingPaintProduction]
        WHERE @ShiftTime IS NULL
        UNION ALL
        SELECT value FROM STRING_SPLIT(@ShiftTime, ',')
        WHERE @ShiftTime IS NOT NULL
    )

内容的提问来源于stack exchange，提问作者M.Bouabdallah