子查询返回多个值.当子查询用作表达式时，不允许这样做

Question

我在SQL Server上有一个存储过程和函数。但是，每当我运行它时，我就会得到这样的错误

子查询返回多个值。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，不允许这样做。

这是我的CekStokTersedia的功能(用于检查可用库存):

USE [Hotel]
GO
/****** Object:  UserDefinedFunction [dbo].[CekStokTersedia]    Script Date: 03/04/2023 12:20:08 ******/
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 @stok float
DECLARE @results TABLE (STOK bit)
INSERT INTO @results (STOK)
SELECT CASE WHEN ISNULL(Stock_Akhir, 0) >= @qty THEN 1 ELSE 0 END AS STOK
FROM Tr_Type_S 
WHERE T_Type = @kodeMenu AND tanggal >= @tglCheckin AND tanggal <= @tglCheckout

DECLARE @result bit
SELECT @result = STOK FROM @results WHERE STOK = 0

-- tambahkan kondisi pengecekan apakah ada data dengan range tanggal yang dimasukkan
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

然后，这是我的存储过程CheckStockMenu:

USE [Hotel]
GO
/****** Object:  StoredProcedure [dbo].[CheckStockMenu]    Script Date: 03/04/2023 12:20:29 ******/
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 (kode varchar(100), qty int);
DECLARE @menuKodeTidakCukup varchar(100) = '';
DECLARE @qtyInt AS int = (SELECT CAST(value AS int) FROM STRING_SPLIT(@qty, ','));
INSERT INTO @menuKodes (kode, qty)
SELECT kode.value, CAST(qty.value AS int)
FROM STRING_SPLIT(@MenuKode, ',') AS kode 
JOIN STRING_SPLIT(@qty, ',') AS qty 
ON kode.[key] = qty.[key]
DECLARE @kodesMenu varchar(100);
DECLARE menu_cursor CURSOR FOR SELECT kode FROM @menuKodes;
OPEN menu_cursor;
FETCH NEXT FROM menu_cursor INTO @kodesMenu;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Pengecekan apakah data dengan range antara @Checkin dan @Checkout ada atau tidak
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
-- Pengecekan stok pada setiap menu yang dimasukkan
ELSE IF dbo.CekStokTersedia(@Checkin, @Checkout, @qtyInt, @kodesMenu) = 0
BEGIN
SET @stokTersedia = 0;
SET @menuKodeTidakCukup = CONCAT(@menuKodeTidakCukup, @kodesMenu, ', ');
END
FETCH NEXT FROM menu_cursor INTO @kodesMenu;
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

然后，像这样运行这个过程:

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

出现如下错误:

子查询返回多个值。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，不允许这样做。

请帮我解决这个问题…

Answer 1

我认为子查询返回多个值-但代码试图将其与单个值进行比较(问题与在某些情况下返回多个值的函数dbo.CekStokTersedia有关)

错误的原因可能是在dbo.CekStokTersedia中使用了@qtyInt参数

@qtyInt是单个整数值—函数内部的查询期望将其与Stock_Akhir中的多个值进行比较(可能导致子查询返回多个值)

• 你可以修改函数内部的查询，使用连接而不是子查询

INSERT INTO @results (STOK)
SELECT CASE WHEN ISNULL(ts.Stock_Akhir, 0) >= @qty THEN 1 ELSE 0 END AS STOK
FROM @tglCheckin AS ci
INNER JOIN @tglCheckout AS co ON ci.idx = co.idx
INNER JOIN Tr_Type_S ts ON ts.tanggal >= ci.tgl AND ts.tanggal <= co.tgl AND ts.T_Type = @kodeMenu

• 我们将Tr_Type_S与两个临时表@tglCheckin和@tglCheckout连接起来，以获得入住和退房日期之间的日期列表
我们检查每个日期的Stock_Akhir值
• 返回@results中的结果(应该返回单个值)

p。您可能想要检查存储过程中游标的使用(它们可能是一个性能问题，有时可能导致意想不到的结果)—最好使用基于集合的操作而不是游标—特别是对于大型数据集

Answer 2

您正在传递@qty的值'2,10'(即两个值)，但尝试将其分配给单个int值:

DECLARE @qtyInt AS int = (SELECT CAST(value AS int) FROM STRING_SPLIT(@qty, ','));

你期望@qtyInt在这里是2还是10，为什么?你需要给SQL Server正确的逻辑来决定选择哪个值，因为@qtyInt只能保存一个值。

虽然看了你的程序，我认为你根本不需要这个分配，你的光标已经包含了这个数量，所以你可以删除问题行，只在光标取回中分配它:

FETCH NEXT FROM menu_cursor INTO @kodesMenu, @qtyInt;

最后，虽然我不熟悉您的所有业务逻辑，但如果使用基于集合的方法不能更有效地重写这些逻辑，我会感到非常惊讶。像这样的过程方法通常不能很好地扩展。