Exception Handling in SQL Server
In this article, I am going to discuss one of the most useful concept that is Exception Handling in SQL Server with some examples. Before going to understand Exception Handling in SQL Server, let’s first understand the need of exception handling with an example.
Example1: Create a procedure for dividing two numbers.
CREATE PROCEDURE spDivideTwoNumber @Number1 INT, @Number2 INT AS BEGIN DECLARE @Result INT SET @Result = 0 SET @Result = @Number1 / @Number2 PRINT 'RESULT IS :'+CAST(@Result AS VARCHAR) END
EXEC spDivideTwoNumber 100, 5
Output: RESULT US :20
EXEC spDivideTwoNumber 100, 0
When we execute the procedure with 100 and 0, we get following error. This is because the divisor value is 0 as we know a number cannot be divided by zero as per the rule of mathematics.
The most important point is, in SQL Server whenever an error occurs at a particular line, it will first print the error message and then continues with the execution. So in the above case when the error occurred in the procedure it will give error message and then continues the execution.
The problem in the above execution is even if the error occurred in the program, it still showing the result so there are chances of users being confused.
Whenever an error occurred while executing a program by using any programming languages such as C#, Java, etc. the program execution terminates abnormally on the line where the error got occurred but in SQL Server it still continues the program execution.
In programming languages (C#, Java) when error occurs, it will skip the execution of all the statements after the error even if those statements are not related with the error where as in SQL Server the execution will not stop when the error occurred, statements related to the error also executed but it should not be happening.
For example in the above stored procedure when the error got occurred it is still displaying the “RESULT IS: 0” which should not happen.
What is Exception handling in SQL Server?
We handle errors of a program both in a programming language as well as databases also whereas handling errors in a programming language needs stopping the abnormal termination and allowing the statements which are not related with the error to execute but handling an error in SQL Server means stopping the execution of the statements which are related to the error.
With the introduction of Try/Catch blocks in SQL Server 2005, the error handling in SQL server is now very much similar to programming languages like C#, and Java. But, before understanding the error handling using the try/catch both, let’s step back and understand how error handling was done in SQL Server prior to 2005, using the system function RAISERROR and @@Error.
Using RAISERROR System Function:
Let’s alter the same stored procedure to use the Exception handling in SQL Server as shown below.
ALTER PROCEDURE spDivideTwoNumber @Number1 INT, @Number2 INT AS BEGIN DECLARE @Result INT SET @Result = 0 IF(@Number2 = 0) BEGIN RAISERROR('Second Number Cannot be zero',16,1) END ELSE BEGIN SET @Result = @Number1 / @Number2 PRINT 'RESULT IS :'+CAST(@Result AS VARCHAR) END END
Let’s execute the procedure by passing the second value as zero.
EXEC spDivideTwoNumber 100, 0
When we execute the above procedure with 100 and 0, it gives us the below error message.
In the above procedure, we are using the Raiserror() function to return an error message back to the calling application, if the second number is zero. We have to pass at least 3 parameters to the Raiserror() function.
RAISERROR(‘Error Message’, ErrorSeverity, ErrorState)
Note: Severity and ErrorState are integers.
In most of the cases, when you are returning custom errors in SQL Server, you need to set the severity level as 16, which indicates this is a general errors and that error can be corrected by the user. In this case, the error can be corrected by passing a nonzero value for the second parameter.
The Error State is also an integer value between 1 and 255. The RAISERROR() function can only generates custom errors if you sett the Error State value between 1 to 127.
In SQL server 2000, in order to detect errors, we use the @@Error system function. The @@Error system function returns a NON-ZERO value if there is an error, otherwise ZERO which indicates that the previous SQL statement executed without any error.
Using @@Error System Function
Let’s modify the stored procedure to make use of @@ERROR system function as shown below.
ALTER PROCEDURE spDivideTwoNumber @Number1 INT, @Number2 INT AS BEGIN DECLARE @Result INT SET @Result = 0 IF(@Number2 = 0) BEGIN RAISERROR('Second Number Cannot be zero',16,1) END ELSE BEGIN SET @Result = @Number1 / @Number2 END IF(@@ERROR <> 0) BEGIN PRINT 'Error Occurred' END ELSE BEGIN PRINT 'RESULT IS :'+CAST(@Result AS VARCHAR) END END
Let’s execute the procedure as shown below
EXEC spDivideTwoNumber 100, 0
When we execute the above procedure by passing 100 and 0, it gives us the below output.
Let’s understand the Predefined Error Terms in Details:
Whenever an error occurs under a program like dividing a number by zero, violation of primary key, violation of Check constraint etc. the system displays an error message telling us the problem encountered in the code.
Every error that occurs in the program is associated with four attributes
- Error Number
- Error Message
- SEVERITY Level
- Error State
Message 8134 (Error Number), Level 16(SEVERITY Level), State 1 (state), Divide by Zero error encountered (Error Message)
ERROR NUMBER: The Error number is a unique identifier given for each and every error that occurs in SQL Server. This value will be below 50,000 for predefined errors and must be above 50,000 for error defined by the user.
ERROR MESSAGE: It is a brief information describing the error occurred which should be maxing from 2047 characters.
This tells about the importance of the error which can be ranging between 0 to 24. In which
0 to 9: are not serves which can be considered as information or status messages.
11 to 16: Indicates these errors can be created by the user.
17 to 19: Indicates these are software errors cannot be corrected by the user must be reported to the system administrator.
20 to 24: Indicates fatal errors and if these errors occur they can damage the system or database. So here the connection immediately terminates with the database.
STATE: It is an arbitrary value which is not that important can be ranging between 0 to 127. We use this whenever the same error has to occur in multiple places.
NOTE: We can find the information of all predefined errors under the table “Sys Messages”
We will discuss the real-time example of using RaiseError and @@Error function in SQL Server but before that, we need to understand Transaction Management in SQL Server as without Transaction Management the use of Error Handling is useless. So in the next article, I am going to discuss the basics of Transaction Management in SQL Server with some examples.
In this article, I try to explain the Exception Handling in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.