Try Catch w/Transaction and executing stored procedures

Question

I need to create an error-handling method for a couple of Stored Procedures.

Proc1

CREATE PROCEDURE dbo.Proc1
AS
BEGIN TRY
    BEGIN TRANSACTION 

    EXEC Proc2

    INSERT INTO tableA (col1, col2) values (1,GETDATE())
    
    --do something that causes error
    SELECT 1/0

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH

Proc2

CREATE PROCEDURE dbo.Proc2
AS
BEGIN TRY
    BEGIN TRANSACTION 

    INSERT INTO tableB (col1,col2,col3) values (1,'John Doe',53)

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH

The reason I need nested try/catch transactions is that Proc2 can be called without proc1, but proc1 will always call proc2.

My question is, will that insert into tableB be rolled back? If not, how can I make it roll back?

Answer 1

This is my stored procedure template: https://spaghettidba.com/2011/07/08/my-stored-procedure-code-template/

As the blog post says, it's based on avoiding nested transactions, which are illusory (it's basically just a transaction counter that gets increased).

It's quite an old blog posts, but there's not much I would change there. Maybe I would use THROW rather than RAISERROR, but the main concepts still stand.

Answer 2

will that insert into tableB be rolled back?

Yes. When called from Proc1, Proc2's BEGIN/COMMIT creates a "nested transaction" so the COMMIT isn't a real commit, it just decrements @@trancount.

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.