Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 rolling back inserts

Author  Topic 

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-16 : 18:10:49
I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 18:46:00
[code]
INSERT 1

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END

INSERT 2

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END

INSERT 3

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END

INSERT 4

IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN

RETURN

[/code]

You can use GOTO with labels to avoid the repetitive code. Check out GOTO in SQL Server Books Online for more details.

Tara Kizer
aka tduggan
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-17 : 00:31:49
Read this to know more error handling techniques
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 01:26:36
Tara: Do you need a BEGIN TRANSACTION before INSERT 1 (or is it implicit perhaps)?

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-17 : 12:39:41
Of course!

BEGIN TRAN

INSERT 1
...

Tara Kizer
aka tduggan
Go to Top of Page
   

- Advertisement -