September 2, 2008 at 10:41 am
Hi Guys, can I use multiple statements in my BEGIN TRANSACTION case so that they will ALL rollback in the event of an error?
For example:
BEGIN TRANSACTION
do this 1
do this 2
do this 3
SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
where all 3 do this must be completed, and if there is an error, to rollback all three. All of the examples I have seen deal with one statement in the case, and in my case, all three must be done before I commit. I can't have the first two work fine, then the third implode, and then have my data in the first two places and not the third. . .
September 2, 2008 at 10:53 am
Yes, you can do that. All three statements will be rolled back.
Greg
September 2, 2008 at 11:41 am
Yes, however...
@@Error is set by every statement to the error that statement threw, or 0 if no error occured. In your example, if doSomething1 or DoSomething2 threw an error, you would never know and the commit would happen, not the rollback. Only if DoSomething3 throws an error will your error detection pick it up.
If you're on SQL 2005 (I assume so, based on the forum this is in) this is a much prefered, much easier way...
BEGIN TRANSACTION
BEGIN TRY
do this 1
do this 2
do this 3
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
If an error occurs within a try block, execution is immediatly transfered to the catch and no further instruction in the try block are executed.
If you want to do your error checking with @@Error, you need to check the value of @@Error after every statement that may throw an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply