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.                      

Exception Handling in SQL SERVER

First of all let us understand where exception handling can be useful in sqlserver.
I am listing 2 main purpose of it.
1. In transactions it is a must .
2.To return the client some useful/custom error message.

Untill SQL SERVER 2003 exception handling was done by using system variable @@ERROR.But it has its own disadvantages.
EG.
       BEGIN TRANSACTION
               DELETE FROM tablestaff WHERE staffid>30
               IF @@ERROR <> 0
                    BEGIN
                         ROLLBACK
                         RAISERROR ('Error in staff', 16, 1)
                         RETURN
                   END
               DELETE FROM tableEmp WHERE empid<70
               IF @@ERROR <> 0
                   BEGIN
                      ROLLBACK
                      RAISERROR ('Error in Emp', 16, 1)
                      RETURN
                   END
         -- Commit the transaction....
              COMMIT
As seen from above code we can list out major disadvantages of handling exception using @@ERROR variavle .
1. One has to make sure that he/she is not missing to check @@ERROR afterany DML statement.Else we may miss some part while doing error handling which may cause transaction related errors.
2.Stored procedures becomes too long.
3.Diffcult to know exactline number at which error occured. etc....

REMEADY:
SQL SERVER 2005 inducted TRY....CATCH block, much similar to what we are used to in any modern object oriented language.
The above code can be rewritten using TRY...CATCH block as...
          BEGIN TRAN
                BEGIN TRY
                    DELETE FROM tablestaff WHERE staffid>30
                    DELETE FROM tableEmp WHERE empid<70
                    COMMIT
               END TRY
               BEGIN CATCH
                    IF XACT_STATE()<>0
                    ROLLBACK
                    RAISERROR ('Error in line %d, ERROR_SEVERITY(), ERROR_STATE(),ERROR_LINE())
                   END CATCH

Simple stuff !!!(ll explain the functions below soon)
I think the construct is straight forward to be understood.Now lets see what XACT_STATE() stands.
XACR_STATE can have 3 values:
0- No transaction in place currently.
1-Current transaction is a commitable transaction.It means it can be commited.
-1-Current transaction is uncommitable.
Eg:
             BEGIN TRAN
                       BEGIN TRY
                               DELETE FROM tablestaff WHERE staffid>30
                               SELECT 1/0
                               COMMIT
                      END TRY
                      BEGIN CATCH
--XACT_STATE=1 , because error occured when performing a select statement and we can
--commit it in some senarios as this one with no affect to DB table.
                     END CATCH

      If error would have occured while say insertin / deleting operation then XACT_STATE() would have been -1, meaning u cant commit the transaction in any means.

    BEGIN TRAN
                       BEGIN TRY
                               DELETE FROM tablestaff WHERE staffid>30
                               INSER INTO tablestaff values('Enter an int')
                               COMMIT
                      END TRY
                      BEGIN CATCH
--XACT_STATE=-1 , because error occured when performing a insert statement and we can
--commit it in some senarios as this one with no affect to DB table.
                     END CATCH

IMP: If XACT_STATE() is -1 then user cannot perform any operation in database that will affect the current transaction log. These operation can be an insert statement, droping table,rolling back to a saving point etc.Only after rollback he/she can perfor these tasks.

@@TRANCOUNT:
If you see articles in web , many have used this variable instead of XACT_STATE() for rollback. But I felt using XACT_STATE is more appropriate. You can do your own analisys.
@@TRANCOUNT increment by 1 on begin transaction statement. On commit decrements by 1.
On rollback becomes 0.
So it can be used as below in catch block.
IF @@TRANCOUNT>0
ROLLBACK

RAISERROR:It helps to notify the client abt the details of the error , like error message, and othe details passed on to the argument.So if the above SP's called by an .net application , the .net application can able to know where exactly in database error occured.

The syntax is :

RAISERROR(msg id | msg text , severity,state ,<user defined variables>)

Msg id is the error number of the error occurred.It should be greater than 50000 when created by user.By default when we raise errors msg id  generated is 50000.

Now the functions used in catch block.
        ERROR_SEVERITY(): Returns severity level of the error.
        ERROR_NUMBER():returns same as @@ERROR
        ERROR_LINE(): line at which the error occured
        ERROR_PROCEDURE():sp name where error occured
        ERROR_MESSAGE(): full error message that got generated
These help in letting the client know where error has occured.
eg.
SELECT @sev=ERROR_SEVERITY()
SELECT @state=ERROR_STATE()
SELECT @line=ERROR_LINE()
SELECT @msg=ERROR_MESSAGE()
 RAISERROR('error occured at line %d. The error message is : %s',@severity,@state,@line,@msg)

Note the identifiers '%d' '%s' work similar to printf statement in 'C'.

NOTE: These functions will work only in catch block.Elsewhere it will return null.

There is another feature where we can add command 'SET XACT_ABORT' to 'ON' for automatic rollback when an error occurs.This I will explain in a later article.

Also in the next version of sql server code named 'Denali'  a new keyword 'throw' has been added which overcomes some shortcomings of RAISERROR. I have not yet got a chance to try it as I dont have sql server 2012 :-(.

So next time you go for exception handling , keep in mind all these features .