Implementing cascading stored procedures in SQL Server 2008

How can I maintain transactio开发者_如何学Gon across cascading stored procedures?

I am using SQL Server 2008.

You can wrap the whole thing in a transaction, and it will work, but you HAVE to make sure that ALL your child/nested stored procedures will rollback the transaction, otherwise you will cause a deadlock. Something like this:

Create procedure [dbo].[parent] 
as
Begin Transaction
Begin Try
    Exec Child
End Try
Begin Catch
    If @@Trancount > 0
        RollBack
End Catch
Commit 


Create procedure [dbo].[Child] 
as
Begin Transaction
Begin Try
    --Do inserts here
End Try
Begin Catch
    If @@Trancount > 0
        RollBack
    RAISERROR('Error Occured',16,1)
End Catch
Commit