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 occurred at run time are known as Exceptions.
Let us consider an example of table named EmployeeDetails. Here in this table consider the column EmployeeID to be a PrimaryKey.
Now if we try to insert a new row with data where EmployeeID already exist 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 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 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 to handle error
Before going further we should know various error handling system defined functions.
- ERROR_LINE() : It returns the line number at which the error occurred.
- ERROR_MESSAGE() : It specifies 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 name of the stored procedure or trigger where error occurred and returns NULL if error did not occurred 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.
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
SELECT ERROR_LINE() AS ErrorLine,
ERROR_NUMBER() AS ErrorNumber,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState
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.
Download Source code