Use of XACT_ABORT ON

SET XACT_ABORT
As I told in my previous article (http://dotnetpen.blogspot.com/2011/11/exception-handling-in-sql-server.html) I am bak with small article on XACT_ABORT

XACT_ABORT is a command in sql server . When set to ‘ON’ it will rollback any transaction if an error occurred in it.If used where there is no transaction it will just roll back the statement where error occurred and aborts execution.

When a timeout occurs in client or client presses cancel button catch block wont get called . In this case the connection remains open and the transaction also becomes uncommitable.
If we use SET XACT_ABORT to ON then these situation is taken care and sql server roll backs any current transaction.

syntax:
SET XACT_ABORT ON;

please note that it helps to rollback explicit transactions. But we cant handle exceptions ourselves as we do in catch block. Also if used in stored procedures where no transaction is used , it will just stops executing statements after the error generating statement.But any statement before the error generating statement will get commited .

play with below sample to explore more....
eg.
SET NOCOUNT ON;
SET XACT_ABORT ON;  
          BEGIN TRAN
               BEGIN TRY
--RAISERROR('error happened',16,1);                     
              INSERT INTO t1 VALUES(22);
              INSERT INTO t1 VALUES(null);
              COMMIT
          END TRY
         BEGIN CATCH
                    IF XACT_STATE()<>0
                              ROLLBACK 
        END CATCH             

 It is a good practice to set it 'ON' in every stored procedure where transaction is used.