Exception Handling Using Try Catch block in SQL Server

This article explained how you can use the Try-Catch block to handle exceptions in SQL Server. It describes system defined functions which can help you to handle errors.

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


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

        --Sql Statements
        --Sql Statements to handle error 

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

select all rows from table

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

        WHERE TABLE_NAME='EmployeeDetails'            

sql primary key constraint

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.

        INSERT INTO EmployeeDetails
	SELECT 'An error occurred performing this operation '+
			ERROR_MESSAGE() AS ErrorMessage
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_PROCEDURE() AS ErrorProcedure,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() AS ErrorState

It throws an exception and shows error details

sql primary key exception 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.

Source code on Git hub Source Code on Github

Speak your mind
Please login to post your comment!

Blog Search

If you like my content please feel free to buy me coffee. Buy Me A Coffee