SQL query that will be rolled back if any statements fail

Question

I would like to write an SQL script that executes several separate SQL statements; if any of these statements fails, I would like to cancel the entire transaction. So something like:

BEGIN TRANSACTION insert into TestTable values (1) insert into TestTable values (2) insert into TestTabe values (3) --if any of the statements fail ROLLBACK --else COMMIT 

This is for MS SQL 2008. Is there anything I can do for this? Perhaps some kind of exception handling?

I understand that in my example, I could check the TestTable for these values ​​and determine if these statements worked in this way. But actually, my SQL will be much more complicated, and I'd rather digress from knowing what SQL does.

Answer 1

SQL Server has supported exceptions since 2005:

 BEGIN TRY BEGIN TRAN INSERT INTO ... COMMIT TRAN END TRY BEGIN CATCH EXECUTE usp_LogAndRethrowError END CATCH 

Now your LogAndRethrowError can roll back any doomed transactions, a la:

 -- Make sure we are not in a live or 'doomed' transaction IF XACT_STATE() <> 0 ROLLBACK TRANSACTION 

Answer 2

This is one of the ways I've done in the past:

 Declare @HasError int; set @HasError = 0; BEGIN TRANSACTION insert into TestTable values (1) if (@@ERROR != 0) set @HasError = 1 insert into TestTable values (2) if (@@ERROR != 0) set @HasError = 1 insert into TestTabe values (3) if (@@ERROR != 0) set @HasError = 1 if @HasError > 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION 

Answer 3

I am lazy and added this line to all my statements

 SET XACT_ABORT ON 

http://technet.microsoft.com/en-us/library/ms188792.aspx

When SET XACT_ABORT is turned on, if the Transact-SQL operation raises an error at runtime, the entire transaction is completed and rolled back.

When SET XACT_ABORT is turned off, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending on the severity of the error, the entire transaction may be discarded even when SET XACT_ABORT is turned off. OFF is the default.

Compile errors, such as syntax errors, are independent of SET XACT_ABORT.

XACT_ABORT must be set for data changes in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only time this option is not required is if the provider supports nested transactions. For more information, see Common Queries and Distributed Transactions.

Set SET XACT_ABORT at run time or run time, and not at parsing time.