#SQL Try...Catch

Microsoft have introduced the TRY & CATCH syntax (TAC) for error handling is T-SQL, in SQL server 2005. The .NET must have used this syntax before also, as this TAC was initially introduced and now they are introducing this in SQL also

How it works
As I have mentioned this TAC block is very similar to what be use in other.net languages. The idea is fairly simple

When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed.

Begin Try 
     The_Query_for_which_we_need_to_do_the_ Error_Handling 
End Try 
Begin Catch 
   If there is some error in the query within the Try block, this flow 
  will be passed to this Catch block. 
End catch 

If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.

BEGIN TRY           
      Print ' I am level 1 '
          BEGIN TRY
            Print ' I am level 2 '
            SELECT 1/0;    
          END TRY
          BEGIN CATCH    
   SELECT 
   ERROR_NUMBER() AS ErrorNumber,
   ERROR_SEVERITY() AS ErrorSeverity,
   ERROR_STATE() as ErrorState,
   ERROR_PROCEDURE() as ErrorProcedure,
   ERROR_LINE() as ErrorLine,
   ERROR_MESSAGE() as ErrorMessage;   
    Print ' I am going out level 2   ' 
         END CATCH
    Print ' I am level 1 again '    
END TRY
BEGIN CATCH
    -- Execute the error retrieval routine.
        SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;    

END CATCH

OUTPUT
 I am level 1 
 I am level 2 
----------- 
(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134        16            1          NULL               5     Divide by zero 
                                                            error encountered. 
(1 row(s) affected) 
 I am going out level 2   
 I am level 1 again 


In the code above the error is thrown from the Level 2 TAC block 

Error Functions

TRY…CATCH uses error functions to capture error information.

ERROR_NUMBER() returns the error number.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.

ERROR_SEVERITY() returns the error severity.

ERROR_STATE() returns the error state number.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.


Error information is retrieved using these functions from anywhere within the scope of the CATCH block of a TRY…CATCH construct. The error functions will return NULL if called outside the scope of a CATCH block. Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed within the CATCH block. By doing this, it will not be necessary to type error handling code in every CATCH block.