Force rollback to entire script (stored procedure included)

Question

I have a complex script that contains many stored procedures and I need to do a rollback in all cases.

    BEGIN TRANSACTION;  
    
    INSERT INTO Table1 VALUES(1);  
    INSERT INTO Table2 VALUES(2);  
    
    EXEC storedprocedure1
    
    EXEC storedprocedure2
    
    ....
    
    ROLLBACK; 

I have not checked all stored procedures inside (if there is or not other transaction).

I ask if there is a way to rollback the entire script (stored procedures included) independently by presence of other transaction inside the stored procedures.

Thanks!

Answer 1

(Assuming SQL Server)

There is no need to check inside those stored procedures.

The ROLLBACK will rollback all the way to the outer-most transaction, including rolling-back all transactions within storedprocedure1 and storedprocedure2, even if those nested transactions are committed within those procedures.

Selecting @@TRANCOUNT will show you that any ROLLBACK sets the transaction count of the session back to 0.

So if you rollback the outer transaction, anyone who may be expecting those nested transactions to commit is going to be disappointed.