August 20, 2006 at 4:03 pm
HI all
I have a main stored procedure which calls 20 other sprocs each of which does an update, withh below work? in sql2005
proc main
begin trans
exec sproc1 if @rc <> 0 go to errorHandler
exec sproc2 if @rc <> 0 go to error handler
..................
commit trans
return
error_handler
rollback
That is I want to be able to roll back all the updates if anyone does not work?
Thanks
August 21, 2006 at 2:05 pm
What is your Question Exactly?
The above scenario is perfectly valid and if any error occurs in any of the stored procedures all the updates will be rolled back
August 21, 2006 at 2:20 pm
The above scenario is perfectly valid and if any error occurs in any of the stored procedures all the updates will be rolled back
That's suggest what I want, thanks
August 21, 2006 at 2:49 pm
What would happen if there are begin transactions and commit transactions within the SP that are being called.
The following is pseudo code.
SP1
BEGIN TRANSACTION
EXEC SP2 {
BEGIN TRANSACTION
COMMIT TRANSACTION
On Error:
ROLLBACK TRANSACTION
}
EXEC SP3 {
BEGIN TRANSACTION
COMMIT TRANSACTION
On Error:
ROLLBACK TRANSACTION
}
COMMIT TRANSACTION
ON ERROR:
ROLLBACK TRANSACTION
I believe if the called SPs (SP2 & SP3) have begin and commit transactions, they will not be rolled back if the second SP (SP3) fails.
August 21, 2006 at 3:04 pm
In a nested Transaction scenario, If the Outer Most Transaction is rolled back then all the inner transactions (even though they are committed) can be rolled back
Please refer BOL
August 21, 2006 at 3:07 pm
Thanks Gopi Nath.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply