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!