Implementing cascading stored procedures in SQL Server 2008 -

how can maintain transaction across cascading stored procedures?

i using sql server 2008.

you can wrap whole thing in transaction, , work, have make sure child/nested stored procedures rollback transaction, otherwise cause deadlock. this:

  create procedure [dbo].[parent]  begin transaction begin try     exec child end try begin catch     if @@trancount > 0         rollback end catch commit    create procedure [dbo].[child]  begin transaction begin try     --do inserts here end try begin catch     if @@trancount > 0         rollback     raiserror('error occured',16,1) end catch commit