SQL query that'll rollback if any statements fails

Question

I would like to write a SQL script that executes multiple individual SQL statements; if any of those statements fail, I'd like to rollback 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 to accomplish this? Perhaps some kind of exception handling?

I realize in my example I could inspect the TestTable for these values and determine if the statements failed that way. But in reality my SQL will be much more complex and I'd rather abstract myself from knowing what the SQL was doing.

Answer 1

SQL Server has had exception support since 2005:

BEGIN TRY
    BEGIN TRAN

    INSERT INTO ...

    COMMIT TRAN
END TRY
BEGIN CATCH
    EXECUTE usp_LogAndRethrowError
END CATCH

Your LogAndRethrowError can then 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 way I have done this 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 have 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 ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

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

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

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

The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

Answer 4

You might be interested in reading this article: Error Handling in SQL 2005 and Later