如何使用事务 SQL 将临时表放入尝试捕获块中

Question

您好，我有以下查询，其中包含 try catch 块中的事务，如果失败，我会在 catch 块上回滚事务。问题是我正在创建一个临时表，我不知道如何使用事务来处理它。那么在这种情况下，我如何安全地摆脱临时表呢？我试图将其添加到捕获块上，但我不确定这是否是一种好做法

ALTER PROCEDURE GetDataForNewListaAbastecimento
    @UAP NVARCHAR(20),
    @ColaboradorId INT
AS
BEGIN
    DECLARE @TransactionName nvarchar(20) = 'GetDataForNewListaAbastecimento'
    DECLARE @Status INT
    DECLARE @CurrentWeekDay INT
    SET DATEFIRST 1
    SET @CurrentWeekDay = DATEPART (WEEKDAY, GETDATE()) - 1
    CREATE TABLE #tempTable
    (
        Id INT PRIMARY KEY,
        Referencia NVARCHAR(15),
        UAP NVARCHAR(20),
        ConsumoWeek01 FLOAT,
        ConsumoWeek02 FLOAT,
        Stock INT,
        QtdPecasPorCaixa INT    
        UNIQUE (Id)
    )

    BEGIN TRY
    BEGIN TRAN @TransactionName  
        DECLARE @SQL NVARCHAR(MAX)
        SELECT @SQL = 'INSERT INTO #tempTable
        SELECT
            Id,
            Referencia,     
            UAP,
            ConsumoWeek01 AS ConsumoWeek01, 
            ConsumoWeek02, 
            CASE 
                WHEN Stock IS NULL THEN 0
            ELSE    
                Stock
            END AS Stock,
            QtdPecasPorCaixa
        FROM OPENQUERY(MACPAC, 
        ''WITH maxFornecedorByDate AS
         (
            SELECT
                YDA3REP.A3ARCD,
                YDA3REP.A3D5CD,
                ROW_NUMBER() OVER ( PARTITION BY YDA3REP.A3D5CD ORDER BY YDA3REP.A3A3DT DESC) AS Number
            FROM
                AUTO.YSACHAPOR.YDA3REP YDA3REP
         )  
        SELECT 
            ROW_NUMBER() OVER(ORDER BY A.RH6001 ASC) AS Id,
            A.RH6001 as Referencia, 
            A.RH6002 as UAP, 
            A.RH6030 as ConsumoWeek01, 
            A.RH6031 as ConsumoWeek02,
            IC130M.LLBLT1 as Stock,
            M.AUQCON AS QtdPecasPorCaixa
        FROM AUTO.D805DATPOR.TRP060H AS A
        LEFT JOIN AUTO.D805DATPOR.IC130M IC130M 
        ON A.RH6001 = IC130M.LLPPN AND
             IC130M.LLSTLC =
                 CASE A.RH6002
                      WHEN ''''UAP1'''' THEN ''''M1''''
                      WHEN ''''UAP2'''' THEN ''''M2''''
                      WHEN ''''UAP3'''' THEN ''''M3''''
                      WHEN ''''UAP4'''' THEN ''''M4''''
                      WHEN ''''UAP5'''' THEN ''''M5''''
                      WHEN ''''UAP6'''' THEN ''''M6''''
                      WHEN ''''UAPP'''' THEN ''''PROTOS''''
                      WHEN ''''EXT'''' THEN ''''EXTR''''
                    END
        LEFT JOIN 
        (
            SELECT 
                YDAUREP.AUD5CD,
                YDAUREP.AUQCON
            FROM maxFornecedorByDate F
            join AUTO.YSACHAPOR.YDAUREP YDAUREP
            ON F.A3ARCD = YDAUREP.AUARCD
            AND F.A3D5CD = YDAUREP.AUD5CD
            WHERE F.Number = 1 AND YDAUREP.AUD5CD LIKE ''''M%''''
            AND YDAUREP.AUD5CD NOT LIKE ''''%P%''''
            AND YDAUREP.AUA0NB > 1 
            AND YDAUREP.AUG6ST= ''''O''''
        ) M 
        ON M.AUD5CD = A.RH6001      
        WHERE A.RH6001 Not Like ''''FS%''''
        AND A.RH6030 <> 0
        AND A.RH6002 = ''''' + @UAP + ''''' '')'        
        EXEC sp_executesql @SQL
        INSERT INTO hListasAbastecimento (UAP,DataCriacao,ColaboradorId) VALUES (@UAP,GETDATE(),@ColaboradorId)
        INSERT INTO 
            hReferenciasAbastecimento 
            (
                Referencia,
                QtdAbastecimento,
                QtdCaixas,
                QtdPecasPorCaixa,
                ListaAbastecimentoId
            )   
        SELECT 
            C.Id,
            C.Referencia,
            ( T.ConsumoWeek01 / ( ( P.NumDias - @CurrentWeekDay ) * P.NumPAB )) * P.AlcanceAbastecimento AS QtdAbastecimento,
            T.QtdPecasPorCaixa,
            CASE
                WHEN ((T.ConsumoWeek01 / ( ( P.NumDias - @CurrentWeekDay ) * P.NumPAB )) * P.AlcanceAbastecimento) / NULLIF(T.QtdPecasPorCaixa,0) IS NULL THEN NULL
            ELSE
                CAST( CEILING(((T.ConsumoWeek01 / ( ( P.NumDias - @CurrentWeekDay ) * P.NumPAB )) * P.AlcanceAbastecimento) / T.QtdPecasPorCaixa )  AS INT)
            END AS QtdCaixas,
            SCOPE_IDENTITY()
        FROM 
            #tempTable T
        INNER JOIN hParametros P
        ON P.Referencia = T.Referencia 
        AND P.UAP = @UAP
        INNER JOIN hConsumos C
        ON C.Referencia = P.Referencia  
        AND C.UAP = @UAP
        WHERE T.Stock < ( T.ConsumoWeek01 / ( ( P.NumDias - @CurrentWeekDay ) * P.NumPAB )) * P.QtdMin
        ORDER BY QtdAbastecimento DESC
        COMMIT TRANSACTION @TransactionName 
        DROP TABLE #tempTable
        -- Success, Lista de abastecimento foi criada sem errors --
        SELECT @Status = 200
        RETURN @Status
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        ROLLBACK TRAN @TransactionName
        DROP TABLE #tempTable
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_STATE() AS ErrorState,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
            -- Erro, Nao foi possivel criar lista de abastecimento, verificar dynamic query -- 
            SELECT @Status = 400
        RETURN @Status
    END CATCH
END

编辑

我正在使用实体框架核心从 Asp .NET Core 应用程序调用 SP

  await _context.Database
        .ExecuteSqlCommandAsync("EXEC @Status = GetDataForNewListaAbastecimento @UAP, @ColaboradorId", @params);

Answer 1

尝试添加以下内容：

如果OBJECT_ID('tempdb..#Results'( 不是空删除表

#Results

(将命名临时表替换为 #Result(