主页 / dba / 问题 / 313490
Accepted
hap76
Asked: 2022-06-18 09:01:16 +0800 CST

寻找在 IF EXISTS 中作为子查询工作的 CTE 的替代品

  • 772

我有一个 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 的所有引用上都出现了“无法绑定多部分标识符”。

感谢任何提示！

cte upsert

1 个回答

  1. Best Answer

    为什么不尝试将值插入表变量而不是 CTE？只有在导航递归数据集时，CTE 才真正有用。

    BEGIN TRAN;

DECLARE @JSONTable TABLE
(
    StockCode VARCHAR(30)
    ,Qty      DECIMAL(18, 6)
);

INSERT INTO @JSONTable
(
    StockCode
    ,Qty
)
SELECT  StockCode
        ,SUM(Qty) AS Qty
FROM
        OPENJSON(@json)
        WITH (
                 StockCode VARCHAR(30) '$.StockCode'
                 ,Qty DECIMAL(18, 6) '$.Qty'
             )
GROUP BY
        StockCode;

/* Update existing values */
BEGIN
    UPDATE  ur
    SET     ReceivedQty = ReceivedQty - jt.Qty
    FROM    UserReceivedQty AS ur
            INNER JOIN @JSONTable AS jt ON jt.StockCode = ur.StockCode;
END;

/* Insert new values */
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO UserReceivedQty
    (
        StockCode
        ,ReceivedQty
    )
    SELECT  jt.StockCode
            ,jt.Qty
    FROM    @JSONTable AS jt;
END;

COMMIT TRAN;

RETURN @@ERROR;
    • 1

相关问题