Eirikur Eiriksson (11/24/2014)

---

gravitysucks (11/24/2014)

---

create proc proc1 (@param1 int)

as

begin try

declare @param2 int

begin transaction

exec proc2 @param2

commit transaction

end try

begin catch

if @@trancount > 0

rollback transaction

end catch

i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.

Found a couple articles on the net but nothing really suiting my requirement.Please suggest. Thanks.

Quick note, a rollback in a "nested" transaction will rollback all the transactions. There is in fact no such thing as a nested transaction, simply a counter that's iterated. Consider this code sample

😎

USE tempdb;

GO

SET NOCOUNT ON;

SELECT @@TRANCOUNT AS TRANCOUNT;

BEGIN TRAN

BEGIN TRAN

BEGIN TRAN

BEGIN TRAN

SELECT @@TRANCOUNT AS TRANCOUNT;

ROLLBACK TRAN

SELECT @@TRANCOUNT AS TRANCOUNT;

Output

TRANCOUNT

-----------

0

TRANCOUNT

-----------

4

TRANCOUNT

-----------

0