Looking for an alternative to a CTE that will work as a subquery in IF EXISTS

All we need is an easy explanation of the problem, so here it is.

I have an IF EXISTS ‘upsert’ running fine by itself in it’s own stored proc. But when I try and use the same statement referencing a CTE, it doesn’t recognize the CTE. I see in related post that I’m not allowed to use the CTE as the subquery. I’m curious why is that, and how else could I accomplish this?

Working stored procedure using 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

And here is my attempt to repurpose the IF EXISTS in another stored proc which takes a json string as input.

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

This gives me a syntax error after the CTE, and a ‘multipart identifer could not be bound’ on all references to the CTE.

Appreciate any hints!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Why not try inserting values into a table variable instead of a CTE? CTEs are only really helpful when navigating a recursive dataset.

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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0