Can I rollback a transaction that has multiple inner transactions in SQL Server?

I hear if there are nested transactions, the inner transactions will not be committed.

For example, I have a Parent Procedure and about a handful of children procedures each with begin tran/commit/rollback.

Parent Procedure 
- Inner Procedure1 (has being tran/commit/rollback tran)
- Inner Procedure2 (has being tran/commit/rollback tran)
- Inner Procedure3 (has being tran/commit/rollback tran)
- Inner Procedure4 (has being tran/commit/rollback tran)
- Inner Procedure5 (has being tran/commit/rollback tran)

I need to debug the parent procedure but I need to rollback the transaction at the end instead of committing everything it did. It’s a huge procedure.

If I do

    Begin tran 
    - (parent procedure)
      -- Inner Procedure1
      -- Inner Procedure2
      -- Inner Procedure3
      -- Inner Procedure4
      -- Inner Procedure5
    rollback tran

Will everything that happened in all procedures be rolledback assuming no errors anywhere?

Answer

Thank you for the replies. It looks like the answer is yes, everything will be rolledback.

Advertisement

Answer