Stored Procedure Execution of Multiple Scripts

[charmedcharmer]

Hi, I'm not sure what terms to search so I decided to post it as a new thread.

I have a stored procedure with multiple 'update' scripts to multiple tables. Sometimes I do get unexpected errors with one or more of the scripts, but when this scenario happens, all the other scripts run smoothly. How can I prevent this from happening? I want all the scripts execute sequentially and if there is an error in one or more of the scripts then the stored procedure shouldn't run at all.

Thank in advance.

[CVMichael]

What kind of scrips are you talking about ?

Why don't you start a transaction at the beginning of the procedure, and roll back if you get any kind of error ?

[szlamany]

You need to do aggresive tracking of @@ERROR and handle that with ROLLBACK or COMMIT at the end of the SPROC's.

Please show us a bit of your SPROC code so we can assist you with how that would be done.

[CVMichael]

If you don't want to check the @@ERROR every single time you do an insert/update, then you can do it more easier like this:

SET XACT_ABORT ON
BEGIN TRANSACTION
 
INSERT INTO a (b,c) VALUES('aa', 'bb')
INSERT INTO a (b,c) VALUES('aa', 'bb')
INSERT INTO a (b,c) VALUES('cc', 'hh')
 
COMMIT TRANSACTION

It explains about it here: SET XACT_ABORT (Transact-SQL)

It will couse to roll back the entire transaction, when there is any kind of error...