Как удалить временную таблицу в блоке try catch с помощью транзакции SQL

Здравствуйте, у меня есть следующий запрос с транзакцией в блоке try catch, и я откатываю транзакцию в блоке 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

РЕДАКТИРОВАТЬ

Я вызываю SP из приложения Asp .NET Core с Entity Framework Core

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