Exception Handling Using Try Catch block in SQL Server

Whenever we write a query it is checked for syntactical errors before execution. If the syntax is correct it gets compiled and executed. But how does an error becomes an exception? Due to some factors, such as incorrect data an error can occur where the query is syntactically correct. The error that occurred at run time is known as Exceptions.

Let us consider an example of a table named EmployeeDetails. Here in this table consider the column EmployeeID to be a Primary Key. Now if we try to insert a new row with data where EmployeeID already exists it would raise an exception.

The errors generated while executing SQL statements can be handled by using the TRY-CATCH construct.

You can use Try Catch block for any DML statements, any assignments or in the selection of data or with SQL joins or Outer or Cross apply

TRY-CATCH

A TRY-CATCH construct includes a TRY block followed by a CATCH block. If an error occurred in any statement of TRY block the control is passed to the CATCH block, and then the statement in that block is executed.

If there are no errors in the code in the TRY block, then the control is passed to the statement immediately following the END CATCH statement. So, the statements enclosed into the CATCH block are not executed.

Below is the syntax of Try Catch block

    BEGIN TRY
        --Sql Statements
    END TRY
    BEGIN CATCH 
        --Sql Statements to handle error 
    END CATCH
        

Before going further we should know various error handling system defined functions.

• ERROR_LINE(): returns the line number at which the error occurred.
• ERROR_MESSAGE(): specifies the text of the message. The text includes value supplied for any parameters, such as lengths, object names, or times.
• ERROR_NUMBER(): returns error number.
• ERROR_PROCEDURE(): returns the name of the stored procedure or trigger where the error occurred and returns NULL if error did not occur within a stored Procedure or trigger.
• ERROR_SEVERITY(): returns the severity.
• ERROR_STATE(): returns the state of the error.

Example: To see the working of exception handling, let us raise one deliberately. Consider table EmployeeDetails. Here in this table the EmpID column is PrimaryKey. To raise an exception we will try to insert a new row with already provided EmpID.

            SELECT * FROM EmployeeDetails
        

The below given query shows, that table name EmployeeDetails contain Primary Key constraint.

    SELECT CONSTRAINT_NAME,
        TABLE_SCHEMA,
        TABLE_NAME,
        CONSTRAINT_TYPE
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE TABLE_NAME='EmployeeDetails'            
        

Now, when writing the below given code we are deliberately trying to insert a record with the same EmpID which already exists. So, in this case the syntax of the INSERT statement would be correct but the exception would raise when inserting the record. Hence, we will use TRY-CATCH block. In TRY block code is written, whereas in CATCH block the exception would be caught.

    BEGIN TRY
        INSERT INTO EmployeeDetails
    VALUES
        (1002,'Thomas','Scott','Accounts',3)
    END TRY
    BEGIN CATCH
	SELECT 'An error occurred performing this operation '+
			ERROR_MESSAGE() AS ErrorMessage
	SELECT	ERROR_LINE() AS ErrorLine,
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_PROCEDURE() AS ErrorProcedure,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() AS ErrorState
    END CATCH
    GO
        

It throws an exception and shows error details

As specified earlier the ErrorProcedure column provides NULL value as the error is not caught in a Stored Procedure or Trigger.

A TRY-CATCH construct do not catch an error if :

• Severity of error is 10 or lower than 10.
• Severity of error is 20 or greater than that and it stops SQL SERVER task for particular session. In that case session is not stopped and TRY-CATCH construct will handle it.
• Syntax errors also not handled by TRY-CATCH block.
• Try catch block is not allowed in User defined functions.