0

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
7
  • 1
    So, if your procedure is not nested, it will silently eat up the failure? Sounds kinda bad. Otherwise, sure, you can do this, the only thing is that in the article method, you will know that the error came from nested procedure and not from your "own" code i guess. But in my opinion, it doesn't matter, error should be fully rollbacked anyway Commented Jan 22 at 14:35
  • @siggemannen Good point. In my actual stored procedures, I have it selecting either "success" or the error message. I've edited to include that to the example above. Commented Jan 22 at 14:41
  • 2
    Adding this excellent multi-part resource on T-SQL exception handling: sommarskog.se/error_handling/Part1.html Commented Jan 22 at 15:14
  • Not seeing a clear question here Commented Jan 22 at 18:03
  • Do you really need to select a result code before commit or roll back? The responsibility of catching the error ultimately belong to the code calling the procedure anyway, you only need to use the try...catch construct in your SQL to rollback the transaction if needed. Commented Jan 23 at 9:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.