This blog is part of the series The SQL Server TSQL Classes: Stored Procedure

The links to previous post are given below.

In last class I talked about error handling in stored procedure. Continuing on from where I left, in this class I’ll discuss transaction handling and Error handling.

Transaction handling is important so as to make sure that in case of any exception/error in a procedure there aren’t any open transactions resulting in blocking or deadlock situation and thus decreasing the system performance.

Open transaction in a procedure can be handled using @@Trancount global variable or by XACT_STATE().

@@Trancount returns number of begin/open transaction in a connection.

Let’s see how it can be used to handle open transactions.

The procedure uspAddCurrency inserts a value in Adventureworks2008R2.Sales.Currency table. Let’s now force a primary key violation error and analyze the output.


The execution of procedure results in a primary key violation error and thus the commit transaction statement within Try isn’t executed and the control is passed to CATCH block. Inside catch block @@trancount detects one open transaction and thus does a rollback.

Another, way to do this is XACT_STATE() function. It tells whether there is an active committable or active uncommittable transaction. Let’s re-write the above procedure using XACT_STATE().

Let’s now force a primary key violation and analyze the output.


The primary key violation error results in a committable open transaction and it is handled accordingly by the procedure. In order to change the open transaction to uncommittable state we need to set XACT_ABORT to ON.  XACT_ABORT, when set to ON rollbacks the transaction instead of statement if a query results in an error. Let’s make this change in above procedure and analyze the output.


As explained above, the XACT_ABORT renders the transaction to be uncommitable which is trapped by XACT_STATE=-1 condition.

Setting XACT_STATE to ON is considered to be good practice as it rollbacks entire transaction and avoids a situation where in an open transaction result in blocking or deadlock.

Difference between @@TranCount and XACT_STATE()

  • Trancount can’t be used to detect uncommitable transactions.
  • XACT_STATE can be used to either commit or rollback a transaction depending on a situation where as trancount can only be used to rollback transaction.
  • XACT_STATE can’t be used to determine nested transactions.


In this class we learnt how to control transactions with in a stored procedure using @@TRANCOUNT and XACT_STATE function and to use XACT_ABORT to avoid open transactions .



Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook