Exception handling in SQL

Whenever an exception occurs our code gets disrupted. Exception handling is a way to handle these disruptions,  Like we may log when an exception occurs or we may raise error when any exception occurs. We can use TRY and CATCH to handle exceptions.

Syntax

BEGIN TRY  
    SQL statements
END TRY  
BEGIN CATCH  
    Logging errors
END CATCH;  

A simple example of an exception

BEGIN TRY  
    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;  
END CATCH; 

Output after executing above query

1) ERROR_NUMBER() returns the number of the error.
2) ERROR_SEVERITY() returns the severity of error.
3) ERROR_STATE() returns the error state of number.
4) ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
5) ERROR_LINE() returns the line number at which error occurred.
6) ERROR_MESSAGE() returns the text message of error.

You can log these in a table as well.

Errors that are not affected by TRY CATCH

Here, I am trying to get all info from non existing table Employees. Here, error will not be catch.

BEGIN TRY  
    SELECT * FROM Employees 
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;  
END CATCH;  

Output after executing above query

If this error occurred inside stored procedure then it will be caught by CATCH block

Suppose "SELECT * FROM Employees" is inside SP and we call SP from try block then error will be caught at catch block.

Example

Assuming employees table doesn't exists. Execute following SP.

Now, call this SP within try block.

BEGIN TRY  
    EXECUTE GetEmployees 
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;  
END CATCH;  

Output after executing above script