hap76's questions

我有一个 IF EXISTS 'upsert' 在它自己的存储过程中运行良好。但是当我尝试使用引用 CTE 的相同语句时，它无法识别 CTE。我在相关帖子中看到我不允许使用 CTE 作为子查询。我很好奇为什么会这样，我还能如何做到这一点？

使用 IF EXISTS 的工作存储过程：

ALTER Procedure [dbo].[sproc_receive]
    @StockCode VARCHAR(50), 
    @Qty DECIMAL(18,6)
AS

--source: https://weblogs.sqlteam.com/dang/2007/10/28/conditional-insertupdate-race-condition/

SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF EXISTS(SELECT * FROM tblReceivedQty WITH (UPDLOCK, HOLDLOCK) WHERE StockCode = @StockCode)
    BEGIN
          UPDATE tblReceivedQty
          SET ReceivedQty = ReceivedQty + @Qty
          WHERE StockCode = @StockCode
    END
ELSE
    BEGIN
          INSERT INTO tblReceivedQty (StockCode, ReceivedQty)
          VALUES (@StockCode, @Qty)
    END
COMMIT

RETURN @@ERROR
GO

这是我尝试在另一个以 json 字符串作为输入的存储过程中重新调整 IF EXISTS 的用途。

USE [<databasename>]
GO

/****** Object:  StoredProcedure [dbo].[sproc_PutAway]    Script Date: 6/13/2022 4:14:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[sproc_PutAway]
(@json NVARCHAR(MAX) = '')
AS
BEGIN

-- Create CTE from JSON input
WITH json_received(StockCode, Qty)
AS
(
SELECT StockCode, Qty
    FROM OPENJSON(@json)
    WITH (
        StockCode VARCHAR(30) '$.StockCode',
        Qty DECIMAL(18,6) '$.Qty'
        )
)

SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF EXISTS(SELECT * FROM tblReceivedQty WITH (UPDLOCK, HOLDLOCK) WHERE tblReceivedQty.StockCode = json_received.StockCode)
    BEGIN
        UPDATE tblReceivedQty
        SET tblReceivedQty.ReceivedQty = tblReceivedQty.ReceivedQty - (
            SELECT Sum(Qty)
            FROM json_received
            WHERE tblReceivedQty.StockCode = json_received.StockCode
            GROUP BY json_received.StockCode
            )
    END
ELSE
    BEGIN
        INSERT INTO tblReceivedQty (StockCode, ReceivedQty)
        VALUES (json_received.StockCode, (-1 * json_received.Qty))
    END

COMMIT

RETURN @@ERROR
GO

这在 CTE 之后给了我一个语法错误，并且在对 CTE 的所有引用上都出现了“无法绑定多部分标识符”。

感谢任何提示！

我将 JSON 字符串传递给存储过程以插入仓库库存。

有时，同一货盘 (LPN) 上的同一个货架 (LPN) 上会有多个具有相同产品的行项目具有相同的日期。我想将这些汇总到表格中的一行。

JSON 表示已接收并存放到某个位置的各个行。这里有2个相同的项目：

[{"StockCode": "ABC123", "Qty": "200", "Bin": "E4B4_L", "LPN": "1234", "PutDate": "2022-01-21 18:35:53 UTC"}, {"StockCode": "ABC123", "Qty": "400", "Bin": "E4B4_L", "LPN": "1234", "PutDate": "2022-01-21 18:36:43 UTC"}]

所以：

ABC | 200 |  2022-01-21 00:00:00.000 | LPN1234
ABC | 400 |  2022-01-21 00:00:00.000 | LPN1234

应汇总为：

ABC | 600 |  2022-01-21 00:00:00.000 | LPN1234

我尝试在 qty 上进行 GROUP BY 和 SUM，但表中的结果行仍然是 2 个单独的行。由于时间戳，我意识到 PutDate 不一样，所以我认为肯定将其转换为 DATE 会解决它，但事实并非如此。

SQL脚本：

ALTER Procedure spc_PutAway
(@json NVARCHAR(MAX) = '')
AS
BEGIN


INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN)
SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDate AS DATE) as PutDate, GETDATE() as VerDate, LPN
    FROM OPENJSON(@json)
    WITH (
        Bin VARCHAR(20) '$.Bin',
        StockCode VARCHAR(30) '$.StockCode',
        Qty DECIMAL(18,6) '$.Qty',
        PutDate VARCHAR(20) '$.PutDate',
        LPN VARCHAR(50) '$.LPN'
        )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN

END

表中的结果：

Bin StockCode   Qty PutDate VerDate LPN TransID VerCol
E4B4_L  ABC123  200.000000  2022-01-21 00:00:00.000 2022-01-21 10:52:43.823 1234    1   0x000000000000275D
E4B4_L  ABC123  400.000000  2022-01-21 00:00:00.000 2022-01-21 10:52:43.823 1234    2   0x000000000000275E