How to handle runtime errors in SQL Server with XACT_ABORT and XACT_STATE

 

     Using Transaction for your statements can be life saver, sometimes you need to do multiple updates/inserts in a stored procedure. Usually developers wrap these statements in a transaction, If something goes wrong you will have a chance to roll back all the changes to keep the data clean in the database.
     XACT_ABORT option is very useful in a situation like this. It specifies whether SQL Server automatically rolls back the current transaction if any T-SQL statement raises a run-time error. SQL Server has many way to raise errors, Common ways are RAISERROR and THROW commands. You need SQL Server 2012 or later to use THROW also THROW honors XACT_ABORT, RAISEERROR does not.
 

SET XACT_ABORT ON | OFF

• Default setting is OFF, If your statement raises an error, the transaction may roll back depending on its severity.
• When you set XACT_ABORT ON, If any of your SQL statements raise a run-time error the entire transaction will be terminated and rolled back.

XACT_STATE()

    In some cases, you might want to handle the raised errors manually, Raised error might not be severe enough to stop the whole transaction, To do that you need to know if there is any errors raised in your transaction first.
    XACT_STATE is a scalar function. It reports the state of the current running transaction. You might know about @@TRANSCOUNT function which does a similar job. You can use the @@TRANSCOUNT to check if the current request has any active transactions but It can not tell you if the transaction is uncomittable.
 

XACT_STATE returns the following values.

1	Request has an active user transaction
0	Request does not have an active user transaction
-1	Request has an active user transaction which contains uncomittable transactions

Example