Stored Procedure Execution of Multiple Scripts

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.

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 ?

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.

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:

sql Code:

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...