This article will give you a detailed overview of exception handling in SQL Server 2005 / 2008
We can implement exception handling in SQL Server which are similar to the exception handling we are using in C#. A group of T-SQL statements can be added under Try block, if any error occurs under Try block the control will be redirected to the Catch block. This Try - Catch block will handle all the execution time errors which are above 10 in severity level. Syntax errors cannot be handled using this.
Syntax
Begin Try
SQL Statements
End Try
Begin Catch
SQL Statements
End Catch
Below system functions can be used to obtain the error information
- Error_Number() - This will return the error number
- Error_Severity() - This will return the error severity level.
- Error_State() - This will return the error state number
- Error_Procedure() - This will return the name of Stored Procedure, if the error is not raised inside a stored procedure null value will be returned
- Error_Line() - This will return the line number from where the error is occurred.
- Error_Message() - This will return the complete text message of the error occurred.