Exit Gracefully from Stored Procedure and avoid BEGIN/COMMIT mismatch

Question

After having some trouble trapping SQL errors in my VBA application, I redesigned my stored procedures so that if an error occurs, the return value is the error code and an output variable contains the error message. I do not re-throw the error in my catch blocks. I'll call this a "graceful exit" for lack of a better term. It has made things easier on the client-side, but now I have an issue when a trigger fired by a nested stored procedure rolls back a transaction.

Take the below example. TEST_INNER_PROC begins with a @@TRANCOUNT of 1, performs an insert which fires the trigger, which rolls back the transaction, and when TEST_INNER_PROC exits it throws error

266: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Normally, I would pattern both of these procedures the same; I've simplified them here. The inner procedure does not attempt to start a transaction (it wouldn't make a difference), and the outer procedure does re-throw the error, just so that I can see the error information printed. Normally, I would return the error code to the client via the return code and @ERR_MSG output variable.

I like @gbn's pattern here: Nested stored procedures containing TRY CATCH ROLLBACK pattern? However, It does not appear to accommodate my "graceful exit" if the rollback happens in a trigger. I'm also not sure if Rusanu's pattern would accommodate it either.

CREATE TABLE TEST (
    COL1 INT
)
GO

CREATE TRIGGER TEST_TRIGGER
ON TEST FOR INSERT
AS
BEGIN TRY
    THROW 50001, 'TEST Trigger produced an error.', 1
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 AND XACT_STATE()<>0
    ROLLBACK TRAN;
    THROW
END CATCH
GO

CREATE PROC TEST_INNER_PROC
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @RTN INT = 0
BEGIN TRY
    INSERT TEST (COL1) VALUES (1)
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 AND XACT_STATE()<>0
    ROLLBACK TRAN
    SET @RTN = ERROR_NUMBER();
    --THROW
END CATCH
RETURN @RTN
GO

CREATE PROC TEST_OUTER_PROC
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @RTN INT = 0
BEGIN TRY
    BEGIN TRAN
        EXEC @RTN = TEST_INNER_PROC
        IF @RTN <> 0 THROW 50000, 'Execution of TEST_INNER_PROC produced an error.', 1
    COMMIT TRAN
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 AND XACT_STATE()<>0
    ROLLBACK TRAN;
    THROW
END CATCH
GO

EXEC TEST_OUTER_PROC
GO

DROP TABLE TEST
DROP PROC TEST_OUTER_PROC
DROP PROC TEST_INNER_PROC
GO

The above code results in:

Msg 266, Level 16, State 2, Procedure TEST_INNER_PROC, Line 63
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

But if you uncomment the "THROW" statement in TEST_INNER_PROC, it throws:

Msg 50001, Level 16, State 1, Procedure TEST_TRIGGER, Line 69
TEST Trigger produced an error.

which is the error I want to handle in TEST_OUTER_PROC.

Is it possible to use stored procedures that "exit gracefully", returning the error code and error message as variables, and avoid the mismatching number of BEGIN and COMMIT statement?

Exit Gracefully from Stored Procedure and avoid BEGIN/COMMIT mismatch

0 Answers0