Error Handling in SQL Server

Like the programming languages(C# or java) in sql server for error handling we are using the Try catch.

Syntax of try catch block

Begin try --T-SQL statement blocks End try Begin Catch --T-SQL statement blocks End catch

Guideline for using try catch block

1.       A try block must be followed by catch block

If I use only try block its throws error

We try block must be followed by catch block

2.       Each try and catch block must be inside a single batch

It is throwing error because I am not writing the catch block inside the batch.

So it must be in a single batch

3.       Try catch block can be nested.

Begin Begin try                                 Begin try                                 select * from [TestSSIS].[dbo].[emp]                                 End try                                 Begin Catch                                 --T-SQL statement blocks                                 End catch End try Begin Catch                                 Begin try                                 select * from [TestSSIS].[dbo].[emp]                                 End try                                 Begin Catch                                 --T-SQL statement blocks                                 End catch End catch End

Error Function

SQL Server supports the following functions for this purpose:

Ø  ERROR_NUMBER (): The number assigned to the error.

Ø  ERROR_LINE (): The line number inside the routine that caused the error.

Ø  ERROR_MESSAGE (): The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

Ø  ERROR_SEVERITY (): The error’s severity.

Ø  ERROR_STATE (): The error’s state number.

Ø  ERROR_PROCEDURE (): The name of the stored procedure or trigger that generated the error.

Begin Begin try select 10/0 As [Output] End try Begin Catch                 Select ERROR_LINE() AS [Error Line],                                    ERROR_MESSAGE() As [Error Message],                                    ERROR_NUMBER() As [Error Number],                                    ERROR_PROCEDURE() AS [Procedure name],                                    ERROR_SEVERITY() As [Severity Number],                                    ERROR_STATE() AS [Error state] End catch End

  

In the given example error throws error because the divided by zero

Procedure name is NULL because here I am not using any procedure name. If we use Procedure or trigger then it will show the name of it.

Note: Try and catch can’t be used in a User defined function.