Leah
Leah

Reputation: 145

How to properly handle nested try/catch blocks within nested stored procedures?

I have multiple layers of nested stored procedures, each with a try...catch block within them. However, I want each of these nested stored procedures to be able to properly run their own transaction if they're called independently. To manage this, I've been frequently referencing this excellent article.

Now I am working on the error handling piece. If an inner TRY fails, I want it pass the error up to the outermost stored procedure (if one exists) and rollback the entire transaction. In the above mentioned article, the author handles this with an output parameter (@pResCode).

I'm wondering: is there a reason you couldn't instead use the THROW() function in the CATCH block of the inner stored procedure? It seems like a simpler option.

Please reference the article to see his pattern for nested transactions. Below is what I am thinking. Note that the following stored procedures are effectively the same besides the first demonstrating what it looks like to call an inner stored procedure whereas the second does not.

Outer stored procedure:

CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
    -- Causes run-time errors to rollback the entire transaction
    SET XACT_ABORT ON;

    -- Create variable to indicate if SP is nested
    DECLARE @IsNested BIT

    -- Begin try block
    BEGIN TRY
        -- If SP is not nested, begin an explicit transaction and set nested
        -- variable to false. If it is nested, set nested variable to true.
        IF @@TRANCOUNT = 0
        BEGIN
            BEGIN TRANSACTION
            SET @IsNested = 0
        END
        ELSE
            SET @IsNested = 1

        -- Call second stored procedure
        EXEC Procedure2

        -- Maybe do other stuff
        
        -- If stored procedure is not nested, commit the transaction
        IF @IsNested = 0
        BEGIN
            SELECT 'Success' 'DbMsg'
            COMMIT TRANSACTION
        END

    -- End try block
    END TRY

    -- Begin catch block
    BEGIN CATCH
        -- If stored procedure is not nested, rollback the transaction.
        -- If the stored procedure is nested, throw an error to the outer stored procedure.
        IF @IsNested = 0
        BEGIN
            SELECT ERROR_MESSAGE() 'DbMsg'
            ROLLBACK TRANSACTION
        END
        ELSE
        BEGIN
            DECLARE @ErrMsg nvarchar(4000) = ERROR_MESSAGE();
            THROW 50000, @ErrMsg, 1
        END

    -- End catch block
    END CATCH
END

Inner stored procedure:

CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
    -- Causes run-time errors to rollback the entire transaction
    SET XACT_ABORT ON;

    -- Create variable to indicate if SP is nested
    DECLARE @IsNested BIT

    -- Begin try block
    BEGIN TRY
        -- If stored procedure is not nested, begin an explicit transaction and set nested
        -- variable to false. If it is nested, set nested variable to true.
        IF @@TRANCOUNT = 0
        BEGIN
            BEGIN TRANSACTION
            SET @IsNested = 0
        END
        ELSE
            SET @IsNested = 1

        -- Do stuff
        
        -- If stored procedure is not nested, commit the transaction
        IF @IsNested = 0
        BEGIN
            SELECT 'Success' 'DbMsg'
            COMMIT TRANSACTION
        END

    -- End try block
    END TRY

    -- Begin catch block
    BEGIN CATCH
        -- If stored procedure is not nested, rollback the transaction.
        -- If the stored procedure is nested, throw an error to the outer stored procedure.
        IF @IsNested = 0
        BEGIN
            SELECT ERROR_MESSAGE() 'DbMsg'
            ROLLBACK TRANSACTION
        END
        ELSE
        BEGIN
            DECLARE @ErrMsg nvarchar(4000) = ERROR_MESSAGE();
            THROW 50000, @ErrMsg, 1
        END

    -- End catch block
    END CATCH
END

Upvotes: 0

Views: 70

Answers (0)

Related Questions