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?

Erick de Vathaire · Answer 1 · 2022-11-28T17:29:31.050

You can store all the errors in a table and select them from it

CREATE TABLE LOG_ERROR (
    SPID INT DEFAULT @@SPID
    ,DATE DATETIME DEFAULT GETDATE()
    ,ERROR_NUMBER INT DEFAULT ERROR_NUMBER())

In your procedures:

begin try
    --code...
end try
begin catch
    INSERT LOG_ERROR DEFAULT VALUES;
    throw
end catch

After the procedure execution:

SELECT * FROM LOG_ERROR
WHERE SPID = @@SPID
AND DATE > CONVERT(DATE,GETDATE()) --Errors from today

Update: Create a view so you get the text too:

CREATE VIEW VW_LOG_ERROR AS
SELECT
    E.*
    ,M.TEXT
FROM LOG_ERROR E
JOIN SYS.MESSAGES M WITH(NOLOCK) ON E.ERROR_NUMBER = M.MESSAGE_ID
JOIN SYS.SYSLANGUAGES L
    ON M.LANGUAGE_ID = L.MSGLANGID
    AND L.LANGID = @@LANGID
WHERE
    SPID = @@SPID
    AND DATE > CONVERT(DATE,GETDATE()) --Errors from today

Exit Gracefully from Stored Procedure and avoid BEGIN/COMMIT mismatch

1 Answers1