CODEWITHSUNDEEP
sql-server
sobsinha
sobsinha

Reputation: 111

Implementing cascading stored procedures in SQL Server 2008

How can I maintain transaction across cascading stored procedures?

I am using SQL Server 2008.

Upvotes: 1

Views: 1154

Answers (1)

M.R.
M.R.

Reputation: 4837

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

Upvotes: 1

Related Questions