Using RollBack

Question

Using RollBack

andrew krenitz

Newbie

Points: 7
More actions
March 24, 2005 at 6:22 pm

#66746

I have a procedure that post to various tables. This procedure also calls other prodecures. Lets say that after the fifth insert the insert bombs. I want all the transactions completed to that point rolled back including any that happenend in another procedure. I start the transaction with
BEGIN TRAN T1
and end with
COMMIT TRAN T1

How can I accomplish this rollback?

Thanks Andy

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Have you looked at ROLLBACK TRAN T1? You will need to build in @@ERROR check to be the RETURN from the nested procedures and interrogate and IF there is an error use ROLLBACK..

Once you are done with ALL the inserts that is when you should issue the COMMIT..

BOL should be able to assist more as well.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

andrew krenitz Newbie Points: 7 More actions · Answer 2

Do I have to put if @@error != 0

begin

rollback TRAN T1

end

after each insert ?

andrew krenitz Newbie Points: 7 More actions · Answer 3

Do I have to put if @@error != 0

begin

rollback TRAN T1

end

after each insert ?

Jamie Thomson SSChampion Points: 11805 More actions · Answer 4

I would use GOTO. Like this:

BEGIN Tran

stmt1
IF @@ERROR <> 0 GOTO OnError
stmt1
IF @@ERROR <> 0 GOTO OnError
stmt2
IF @@ERROR <> 0 GOTO OnError

GOTO OnSuccess

OnError:
ROLLBACK Tran
GOTO OnEnd

OnSuccess:
COMMIT Tran

OnEnd:

That way all you error handling is in one place (which is a good thing)

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

andrew krenitz Newbie Points: 7 More actions · Answer 5

Thanks for the advice. What happens when nested in these inserts is a call to a procedure. If an error occurs there. What will happen is an insert fails within that?

Jamie Thomson SSChampion Points: 11805 More actions · Answer 6

You need to test for failure in the called proc and pass a value out to the calling code indicating success or failure. Use a similar approach to what I posted before except in the OnError section of your proc put RETURN 1 and in the OnSuccess section put RETURN 0.

You can test for this return value in the calling code like this:

DECLARE @vReturnStatus int
EXEC @vReturnStatus = usp_MyProc
IF @vReturnStatus <> 0 GOTO OnError

which again sends the code to an error handling block in the calling code.

There is a caveat to this. Sometimes the called proc will raise an error with such high severity that the error handling code will not execute and therefore there will be no returned value. For this reason, if you are using @vReturnStatus more than once, make sure you reset it to NULL inbetween each usage!

Hope that helps!

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

andrew krenitz Newbie Points: 7 More actions · Answer 7

Thanks Jamie,

I've incoorporated that in my code and will assume it works. I didn't know that a procedue could return value. My next thing to figure out is how to get SQL to send an email , then triggers.

Thanks Again

Andy

Jamie Thomson SSChampion Points: 11805 More actions · Answer 8

Andy,

Asumption is the mother of all f**k-ups. I'd test it if I were you!

xp_sendmail is used to send emails. I have never used it though so don't know much about it.

Triggers are a fairly big subject. I personally never use of them but you'll get help on all of these subjects from these forums.

Good luck!

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

andrew krenitz Newbie Points: 7 More actions · Answer 9

Jamie,

So far it hasn't blown up. I need the sql administrator to configure the email system. It looked pretty straight forward and it will save time in having the application do it.

Well again thanks for your help

Andy