tsql - SQL怎么做；尝试将错误信息捕获到错误日志表中，以获取在事务中执行的存储过程？

Question

在我存储的过程中，我正在使用Try-Catch，并在Catch块中调用一个错误处理过程，该过程将把错误详细信息记录在ErrorLog表中，然后重新抛出该错误。

在我的C#代码中，我正在使用以下命令执行存储过程:

using(TransactionScope scope = new TransactionScope()) {
    // execute stored procs
    scope.Complete();
}

我遇到的问题是，如果事务中止(从未调用scope.Complete)，我的错误处理存储过程确实会抛出SQL错误，但由于该错误是在上下文中，因此无法将其记录到ErrorLog表中交易;有没有办法解决 !？我已经知道在事务处于不可提交状态时不能插入数据，那么如何退出事务并仍然记录错误？

TSQL代码:

BEGIN PROCEDURE [dbo].[usp_UpsertSomething]
    @SomethingID BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- do something
    END TRY
    BEGIN CATCH
        EXEC dbo.cp_RethrowError
        RETURN -1
    END CATCH;
END

CREATE PROCEDURE [dbo].[usp_RethrowError]
    @ErrorLogID [INT] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted
                                 -- by cp_RethrowError in the ErrorLog table.
AS
BEGIN
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage           VARCHAR(4000),
        @FormattedErrorMessage  VARCHAR(4000),
        @ErrorNumber            INT,
        @ErrorSeverity          INT,
        @ErrorState             INT,
        @ErrorLine              INT,
        @ErrorProcedure         VARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @FormattedErrorMessage = 
        'ErrorLogID %d, Error %d, Level %d, State %d, Procedure %s, ' + 
            'Line %d, Message: '+ @ErrorMessage;

    BEGIN TRY
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1 BEGIN
            SET @ErrorLogID = 0;
        END
        ELSE BEGIN
            INSERT [dbo].[ErrorLog] 
            (
                ErrorNumber, 
                ErrorSeverity, 
                ErrorState, 
                ErrorProcedure, 
                ErrorLine, 
                ErrorMessage
            ) 
            VALUES 
            (
                @ErrorNumber,
                @ErrorSeverity,
                @ErrorState,
                @ErrorProcedure,
                @ErrorLine,
                @ErrorMessage
            );

            -- Pass back the ErrorLogID of the row inserted
            SELECT @ErrorLogID = @@IDENTITY;
        END
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure cp_RethrowError.';
    END CATCH

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
    (
        @FormattedErrorMessage, 
        @ErrorSeverity, 
        1,
        @ErrorLogID,     -- parameter: ErrorLogID in ErrorLog table.
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
    );
END

Answer 1

由于事务是全有或全无的事务，因此完成我想要完成的工作的唯一方法是在调用我的错误处理调用之前，在Catch块内部调用ROLLBACK TRANSACTION。

我的错误日志最终以我要记录的错误结尾，但是在我的.Net catch块中，我最终收到来自SQL的投诉:“EXECUTE之后的事务计数表明缺少COMMIT或ROLLBACK TRANSACTION语句。先前的计数= 1，当前计数=0。”

我可以忍受这一点!