Try/Catch and Transactions

Try/Catch-blocks and Transaction-Blocks really don’t play nice together. Within nests for example an outer @@ROLLBACK TRANSACTION will rollback both inner and outer transaction-blocks regardless of already executed COMMIT TRANSACTION statements.

This is the most robust template I could come up with for nested Try/Catch-blocks that contain Transactions.

BEGIN TRY
BEGIN TRY
(inner DDL statements)
begin transaction
(inner DML statements)
commit transaction
END TRY

BEGIN CATCH
(inner error capture statements)
rollback transaction
END CATCH
(outer DDL statements)
begin transaction
(outer DML statements)
while @@trancount > 0 commit transaction
END TRY

BEGIN CATCH
(outer error capture statements)
while @@trancount > 0 rollback transaction
END CATCH