Rollback on error in SQL Server 2008

Question

I have a stored procedure in a SQL Server 2008 database. This stored procedure is responsible for updating records in tables across about a dozen tables. The basic idea is show here:

UPDATE Table1 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table2 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table3 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table4 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table5 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table6 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table7 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table8 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table9 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table10 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table11 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table12 SET [Field1]=@newValue WHERE [Field1]=@originalValue

There is a possibility where a foreign key constraint is violated. Or some other potential errors. Because of this, I wanted to wrap all of these in some sort of transactional unit. In the event that some error occurred, I just wanted to basically restore the records to their values before this block was executed. I thought a transaction was designed to do this. However, I can't seem to find an example of what I'm trying ot do.

Can someone provide an example of how I can accomplish what I'm trying to do? Or do I misunderstand the purpose of a transaction?

Thanks!

Answer 1

I think Thomas' answer will do what you need the easiest, so I've voted for that one. But just for completeness sake, here's a basics example of how rollback/commmit/transaction logic generally works in stored procedures:

Basically, you need to wrap your SQL in a transaction, and then roll back if there's an error after any statement, or commit at the end if there wasn't. Something like this:

BEGIN TRANSACTION

UPDATE Table1 SET [Field1]=@newValue WHERE [Field1]=@originalValue
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table2 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table3 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table4 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table5 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table6 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table7 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table8 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table9 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table10 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table11 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

UPDATE Table12 SET [Field1]=@newValue WHERE [Field1]=@originalValue;
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) GOTO PROBLEM

COMMIT TRANSACTION

PROBLEM:
IF (@errorCode <> 0) BEGIN
RAISERROR ('Error in stored procedure.', 16, 1)
RETURN @errorCode
ROLLBACK TRANSACTION

Answer 2

Set Xact_Abort On;
GO
Begin Transaction;

UPDATE Table1 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table2 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table3 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table4 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table5 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table6 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table7 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table8 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table9 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table10 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table11 SET [Field1]=@newValue WHERE [Field1]=@originalValue
UPDATE Table12 SET [Field1]=@newValue WHERE [Field1]=@originalValue

Commit Transaction;

Set Xact_Abort On ensures that if any statement failes, the entire transaction rollsback.

SET XACT_ABORT (Transact-SQL)

Answer 3

See Example C in the TRY...CATCH topic in BOL.