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.
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.
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+
Using If-Else Statement in T-SQL(SQL Server) How to Declare and Assign a Variable in SQL Server Creating, Altering, Dropping and executing Sproc or Stored Procedure in MS SQL Server