Exception Handling in SQL Server
In this article, I am going to discuss Exception Handling in SQL Server with some examples. As a developer, it is very important for you to understand Exception Handling in SQL Server. As part of this article, we are going to discuss the following pointers related to SQL Server Exception Handling.
- Why do we need Exception Handling in SQL Server?
- What Happens in SQL Server when an Exception Occurred?
- What is Exception handling in SQL Server?
- Exception Handling Using RAISERROR System Function.
- Understanding the RaiseError System Function in SQL Server.
- Using @@Error System Function in SQL Server
- Understanding Error Number, Error Message, Severity Level, and Error State
Why do we need Exception Handling in SQL Server?
Let us understand the need for exception handling with an example. So, create a SQL Server Stored Procedure for dividing two numbers as shown below.
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
Execution: EXEC spDivideTwoNumber 100, 5 Output: RESULT IS: 20
EXEC spDivideTwoNumber 100, 0
When we execute the procedure with 100 and 0, we get the 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.
As you can see in the above image, in SQL Server whenever an error occurs at a particular line, it will first print the error message and then continues with its execution. So in the above case when the error occurred in the procedure it will give an error message and then continues the execution and this is the reason why it still prints the message “RESULT IS :0”.
So, the problem with the above execution is even if an error occurred in the program, it still showing the result so there are chances of users being confused.
What Happens in SQL Server when an Exception Occurred?
In SQL Server, whenever an exception occurred, it displays the exception message and then continues the program execution. But in programming languages such as C#, Java, etc. whenever an error occurred then the program execution terminates abnormally on the line where the error got occurred. 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. 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.
Exception Handling Using RAISERROR System Function:
Let’s alter the same stored procedure that we created in the previous example as shown below to use the Raiseerror System Function to handling the exception in SQL Server.
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.
Understanding the RaiseError System Function in SQL Server:
The RaiseError System Function takes 3 parameters as shown below.
RAISERROR(‘Error Message’, ErrorSeverity, ErrorState)
Error Message: The custom error message that you want to display whenever the exception raised.
Error Severity: In most of the cases, when you are returning custom errors in SQL Server, you need to set the ErrorSeverity level as 16, which indicates this is a general error and the error can be corrected by the user. In our example, the error can be corrected by passing a nonzero value for the second parameter.
Error State: The ErrorState is also an integer value between 1 and 255. The RAISERROR() function can only generate 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 in SQL Server:
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 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 piece of brief information describing the error occurred which should be maxing from 2047 characters.
Severity Level: 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 and how to use try-catch to handle error 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.