I Would Like To Mantain Multiple Transaction.In My Case I Have Master SP Which calls 16 SPs.Now I Would Like To Mantain Single Transaction In This Master SP.So If One Of SP Throw Error Then It Will Roll Back All The 16 SP Data.Please Guide Me How To Achive
This.Thanks In Advance
You only have one transaction that all SPs are called in, and you have to check the @@ERROR status after each SP call (or do something more logical like if you expect one row to be updated but none are, or 100 are, then treat that as an error) and rollback
as needed after each step
RollerCoster
Member
542 Points
191 Posts
Maintain Multiple Transaction
May 30, 2012 12:13 PM|LINK
Hello Friends,
I Would Like To Mantain Multiple Transaction.In My Case I Have Master SP Which calls 16 SPs.Now I Would Like To Mantain Single Transaction In This Master SP.So If One Of SP Throw Error Then It Will Roll Back All The 16 SP Data.Please Guide Me How To Achive This.Thanks In Advance
transaction
Mark As Answer If It Helps You
AidyF
Star
9246 Points
1576 Posts
Re: Maintain Multiple Transaction
May 30, 2012 12:25 PM|LINK
Easiest way is to maintain the transaction in code. Start it running, then execute your SPs and rollback or commit
http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET
kashifilyaz
Participant
1144 Points
198 Posts
Re: Maintain Multiple Transaction
May 30, 2012 12:37 PM|LINK
BEGIN TRY BEGIN TRANSACTION //call sp 1 // call sp 2 COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK END CATCHRollerCoster
Member
542 Points
191 Posts
Re: Maintain Multiple Transaction
May 30, 2012 02:22 PM|LINK
@AidyF: I Need Solution In SQL Server
Mark As Answer If It Helps You
RollerCoster
Member
542 Points
191 Posts
Re: Maintain Multiple Transaction
May 30, 2012 02:24 PM|LINK
@kashifilyaz In Your Solution how can you mantain all 16 SP Transaction Individually And Rollback All SP If Get Erro In On SP?
Mark As Answer If It Helps You
AidyF
Star
9246 Points
1576 Posts
Re: Maintain Multiple Transaction
May 30, 2012 03:58 PM|LINK
You only have one transaction that all SPs are called in, and you have to check the @@ERROR status after each SP call (or do something more logical like if you expect one row to be updated but none are, or 100 are, then treat that as an error) and rollback as needed after each step
http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling