Início / dba / Perguntas / 313490
Accepted
hap76
Asked: 2022-06-18 09:01:16 +0800 CST

Procurando uma alternativa para um CTE que funcione como uma subconsulta em IF EXISTS

  • 772

Eu tenho um 'upsert' IF EXISTS funcionando bem sozinho em seu próprio proc armazenado. Mas quando tento usar a mesma instrução referenciando um CTE, ele não reconhece o CTE. Vejo no post relacionado que não tenho permissão para usar o CTE como a subconsulta. Estou curioso por que é isso, e de que outra forma eu poderia fazer isso?

Trabalhando o procedimento armazenado usando 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

E aqui está minha tentativa de redirecionar o IF EXISTS em outro proc armazenado que recebe uma string json como entrada.

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

Isso me dá um erro de sintaxe após o CTE e um 'identificador de várias partes não pôde ser vinculado' em todas as referências ao CTE.

Aprecie todas as dicas!

cte upsert

1 respostas

  1. Best Answer

    Por que não tentar inserir valores em uma variável de tabela em vez de um CTE? Os CTEs são realmente úteis apenas ao navegar em um conjunto de dados recursivo.

    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

relate perguntas