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.
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.
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 :