How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>