How to handle errors in SQL Server By Sagar Jaybhay

In this article we will understand How to handle errors in SQL Server By Sagar Jaybhay.

Handle Error

In SQL Server 2005 they introduced try/catch block in SQL server likes C# and Java.

In SQL Server 2000 they have syntax -- @@Error

In SQL Server 2005 they have introduced – try/catch.

In SQL server the variables which are starting with @@ symbols are called global variables but hey are not variables but working like variables but they are similar to a function.

Throw an error in SQL Server we have a function, Raiserror(error_message, severity level, state)

In this Raiserror function, the first parameter is error_message which we want to display. Like throw keyword in C#.

The second parameter is Error Severity level- which is most cases is 16 means the user can resolve this error.

The third Parameter is State: It is a number between 1 to 255 but Raiserror will generate state between 1 to 127.

@@ERROR is a system function that contains non zero value if there is no error else it has 0 value.

@@ERROR is cleared and reset on each statement of execution.

Try/ Catch:

Whatever we can do with @@ERROR we can achieve it by using a try-catch block. You can write any number of statement inside the try block and if any error occurred then control directly moves in the catch block and the rest of the statement in try blocks are the skip. If no error will occur then the control bypass/skip the execution of the catch block.

Errors that are trapped in the catch block are not returned to calling function for that you need to use Raiserror function.

You can use system function which gives more information about the error and this can be called inside catch block only.

To write code in try-catch block use below syntax here

Begin Try
//-- Your code is here
End Try
Begin Catch
//-- Your code is here

End Catch

In SQL server to get more information SQL server provides functions for that which is described below

1. Error_Number() : display how many errors occurred
2. Error_message() : It returns the message of error    
3. Error_Procedure(): returns the name of the stored procedure or trigger where an error occurs
4. Error_State(): returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block
5. Error_Severity(): returns the error severity value of an error, regardless of how many times it runs or where it runs within the scope of the CATCH block
6. Error_Line (): returns the line number at which the error occurred. 

Above all of these functions are run inside the context of the catch block. Outside the catch block, it will return null.

GitHub: https://github.com/Sagar-Jaybhay