Transact-SQL - Using Try-Catch in Stored Procedure and Error Functions

Try-Catch is used to handling exception in C#. Today we will see that how this is also useful to handle errors in SQL also. The Try-Catch consists of two blocks Try block and Catch block. We just write our statements inside the Try block and when the error generate in the Try block the control is passed to Catch block and there we handle the error by writing statements.

When we use the Try-Catch blocks in T-SQL we need to remember that the Catch block will be written after the Try block so that the error that generates in Try block can be handled immediately by the Catch block.


         DELETE from Student_Master
         WHERE id=5001
         SELECT  ERROR_NUMBER() as errornumbers;

Note: the ERROR_NUMBER() is the error functions that is predefined in T-SQL library.

Error Functions:

The error functions are used to capture error information that arrives from the Try-Catch statements. The Try-Catch uses the following error functions to keep the error information:

1. ERROR_NUMBER(): This function returns the error number. So that the error can be detected from the predefined error number in SQL.

2. ERROR_MESSAGE(): This function returns the complete text of the error message including length, object names or times.

3. ERROR_SEVERTY(): This function returns the severty of error. If the severty in between 10 to 20 then Try-Catch can handle the error easily. 

4. ERROR_STATE(): This function the error state numbers.

5. ERROR_LINE(): It returns the line number from the routine where the error generated.

6. ERROR_PROCEDURE(): It returns the name of procedure or trigger where the errors generated.

So these are the error functions that we can use inside the SQL Try_Catch block. Also we can use nested Try-Catch block in T-SQL. Nesting Try-Catch block means we can use the Try-Catch block inside the other Try-Catch blocks.

Author Bio: : Itender Rawat

He is Part time Blogger, Designer, Movie freak and IT Technician by Profession. He likes to write on Technology, Blogging and useful Internet Tricks on To know more about him : Connect with him on Google+

admin (158 Posts)

Leave a reply

Your email address will not be published.

Comment moderation is enabled. Your comment may take some time to appear.