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

1 vote
1 answer
705 views
                          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 []
    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!




                        
Asked by hap76 (15 rep)
Jun 17, 2022, 05:01 PM
Last activity: Jun 22, 2022, 04:37 PM
Looking for an alternative to a CTE that will work as a subquery in IF EXISTS

Related Questions
