SQL Server存储过程报错：子查询返回多个值问题求助

错误信息 #

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

错误原因分析 #

1. 存储过程变量赋值错误：
   存储过程中DECLARE @qtyInt AS int = (SELECT CAST(value AS int) FROM STRING_SPLIT(@qty, ','));语句，当传入的@qty包含多个值（如示例中的'2,10'）时，STRING_SPLIT返回多行结果，直接赋值给单个int变量会触发报错。此外，该变量逻辑错误，每个菜单对应独立需求量，不应使用全局变量存储。

2. 自定义函数逻辑冗余：
   函数CekStokTersedia使用临时表存储每日库存检查结果，写法冗余，可通过聚合函数简化判断逻辑，避免不必要的临时表操作。

修复方案 #

1. 修复存储过程CheckStockMenu #

移除错误的全局变量@qtyInt，在循环中从临时表获取当前菜单对应的需求量；优化字符串拆分关联逻辑，兼容SQL Server 2017及更早版本：

USE [Hotel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC [dbo].[CheckStockMenu] '2023-04-01', '2023-04-03', 'PA03,PA02', '2,10'
ALTER PROCEDURE [dbo].[CheckStockMenu]
    @Checkin datetime,
    @Checkout datetime,
    @MenuKode varchar(100),
    @qty varchar(100)
AS
BEGIN
     SET NOCOUNT ON;
    DECLARE @stokTersedia bit = 1;
    DECLARE @menuKodes TABLE (rn int, kode varchar(100), qty int);
    DECLARE @menuKodeTidakCukup varchar(100) = '';

    -- 拆分菜单和数量并关联，用ROW_NUMBER兼容旧版本SQL Server
    INSERT INTO @menuKodes (rn, kode, qty)
    SELECT 
        k.rn, k.value, CAST(q.value AS int)
    FROM (
        SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
        FROM STRING_SPLIT(@MenuKode, ',')
    ) AS k
    JOIN (
        SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
        FROM STRING_SPLIT(@qty, ',')
    ) AS q ON k.rn = q.rn

    DECLARE @kodesMenu varchar(100);
    DECLARE @currentQty int;
    -- 游标同时获取菜单编码和对应需求量
    DECLARE menu_cursor CURSOR FOR SELECT kode, qty FROM @menuKodes;
    OPEN menu_cursor;
    FETCH NEXT FROM menu_cursor INTO @kodesMenu, @currentQty;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 检查日期范围内是否每天都有库存记录
        IF (SELECT COUNT(tanggal) FROM Tr_Type_S WHERE T_Type = @kodesMenu AND tanggal >= @Checkin AND tanggal <= @Checkout) <> DATEDIFF(day, @Checkin, @Checkout) + 1
        BEGIN
            SET @stokTersedia = 0;
            SET @menuKodeTidakCukup = CONCAT(@menuKodeTidakCukup, @kodesMenu, ', ');
        END
        -- 调用函数检查当前菜单的库存是否满足对应需求量
        ELSE IF dbo.CekStokTersedia(@Checkin, @Checkout, @currentQty, @kodesMenu) = 0
        BEGIN
            SET @stokTersedia = 0;
            SET @menuKodeTidakCukup = CONCAT(@menuKodeTidakCukup, @kodesMenu, ', ');
        END
        FETCH NEXT FROM menu_cursor INTO @kodesMenu, @currentQty;
    END
    CLOSE menu_cursor;
    DEALLOCATE menu_cursor;
    
    IF @stokTersedia = 1
        SELECT 'Stok tersedia' AS Status;
    ELSE
        SELECT CONCAT('Stok tidak cukup untuk menu dengan kode ', LEFT(@menuKodeTidakCukup, LEN(@menuKodeTidakCukup) - 1)) AS Status;
END

2. 修复自定义函数CekStokTersedia #

简化逻辑，直接通过聚合函数判断日期范围内是否存在库存不足的情况，移除临时表操作：

USE [Hotel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CekStokTersedia] (@tglCheckin datetime, @tglCheckout datetime, @qty int, @kodeMenu varchar(100))
RETURNS bit
AS
BEGIN
    DECLARE @result bit = 1;

    -- 检查日期范围内是否有任何一天库存不足
    IF EXISTS (
        SELECT 1 
        FROM Tr_Type_S 
        WHERE T_Type = @kodeMenu 
          AND tanggal >= @tglCheckin 
          AND tanggal <= @tglCheckout
          AND ISNULL(Stock_Akhir, 0) < @qty
    )
        SET @result = 0;

    -- 检查日期范围内是否存在缺失的记录
    IF NOT EXISTS (
        SELECT 1 
        FROM Tr_Type_S 
        WHERE T_Type = @kodeMenu 
          AND tanggal >= @tglCheckin 
          AND tanggal <= @tglCheckout
    )
        SET @result = 0;

    RETURN @result
END

测试执行 #

执行原测试语句验证修复效果：

EXEC [dbo].[CheckStockMenu] '2023-04-01', '2023-04-03', 'PA03,PA02', '2,10'

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