http://sqlsolace.blogspot.com/2013/01/transaction-error-control-xactabort.html Transaction error control - XACT_ABORT & XACT_STATE (SQL 2012) XACT_ABORT can be used to control the behaviour of SQL when an error in a transaction occurs. (it has been around since SQL 2000) SET XACT_ABORT ON If a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. SET XACT_ABORT OFF Only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. XACT_STATE is new to SQL 2012. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. XACT_STATE() = 1 - Committing the transaction will suceed XACT_STATE() = -1 - Committing the transaction will fail XACT_STATE() = 0 - No user transaction for request. SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- Do a DML operation - Insert, Delete or Update COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN PRINT 'Failed, Rollback occuring' ROLLBACK TRANSACTION; END; IF (XACT_STATE()) = 1 BEGIN PRINT 'Succeeded, Transaction committed' COMMIT TRANSACTION; END; END CATCH; GO
