Using XACT_ABORT with transactions

XACT_ABORT can be used for an error handling to roll back all changes to original if there is any error occur in transactions. One of the main problem with XACT_ABORT as an error handling is it is difficult to trap errors and capture error numbers and returning back to the calling application. If you are not familiar with error numbers please refer my previous blog post about error numbers using the following link (Error Numbers) .
Let us see how we could use XACT_ABORT as error control and a workaround to solve the previously stated problem in action:

--1 Intialization
IF OBJECT_ID('TESTTABLE','U') IS NOT NULL
DROP TABLE TESTTABLE;
GO
 
SELECT * INTO TESTTABLE
FROM tContract;
 
SELECT COUNT(* ) TotalCount
FROM TESTTABLE;
GO

--2 With XACT_ABORT OFF
SET XACT_ABORT OFF; 
GO
BEGIN TRAN
	DELETE TOP(3)
	FROM TESTTABLE
	IF @@ROWCOUNT

Using XACT_ABORT with transactions Step1

-- Check the test table
SELECT COUNT(* ) TotalCount
FROM TESTTABLE
GO

Using XACT_ABORT with transactions Step2
In this case the after transaction rollback when we check the number of rows we have 16 rows. That means the first 3 records deleted before the error were not rollback.

--3 With XACT_ABORT ON and without TRY-CHATCH --
 
SET XACT_ABORT ON; 
GO 
 
BEGIN TRAN
 
	DELETE TOP(3)
	FROM TESTTABLE
	IF @@ROWCOUNT

Using XACT_ABORT with transactions Step3

-- Check the test table
SELECT COUNT(*) TotalCount
FROM TESTTABLE
GO

Using XACT_ABORT with transactions Step4
Now we have 19 rows and none of the rows are missing but we still can’t control the error or any statement in the batch after the error could not be executed.

To avoid this problem we could use XACT_ABORT with TRY-CATCH. In the catch statement we have to rollback to avoid this error. To check if there is an error or now we could use @@TRANCOUNT OR a scalar function XACT_STATE(). XACT_STATE() has three values (1) There is active user transaction that could commit or roll back, (0) There is no active user transaction and (-1) There is active user transaction and an error makes it become uncommittable.

--4 With XACT_ABORT ON and with TRY-CHATCH --
 
SET XACT_ABORT ON; 
GO 
BEGIN TRY
	BEGIN TRAN
 
		DELETE TOP(3)
		FROM TESTTABLE
		IF @@ROWCOUNT  0 ROLLBACK TRANSACTION;
	IF  XACT_STATE() = -1 ROLLBACK TRANSACTION; 
	THROW;
END CATCH
GO
SET XACT_ABORT OFF; 
GO

-- Check the test table
SELECT COUNT(*) TotalCount
FROM TESTTABLE
GO

Using XACT_ABORT with transactions Step6
Using the try catch we were able to roll back all changes back to the original and control the error message.