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.

Sysnax:

BEGIN TRY
         DELETE from Student_Master
         WHERE id=5001
END TRY
BEGIN CATCH
         SELECT  ERROR_NUMBER() as errornumbers;
END CATCH

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 Tricksmode.com To know more about him : Connect with him on Google+

admin (155 Posts)


Tagged with →  

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Set your Twitter account name in your settings to use the TwitterBar Section.