Back to: Dot Net Interview Questions and Answers
SQL Server Exception Handling Interview Questions And Answers
In this article, I am going to discuss the most frequently asked SQL Server Exception Handling Interview Questions and Answers. Please read our previous article where we discussed the most frequently asked SQL Server TCL Interview questions. As part of this article, we are going to discuss the following SQL Server Exception Handling Interview Questions with answers.
- Why we need Exception Handling in SQL Server?
- What is Exception Handling?
- Error handling in SQL Server 2005 and later versions
- How to handle errors in SQL Server?
- What is ERROR_MESSAGE()?
- How to raise errors explicitly in a program?
- What is the difference between the RAISERROR function and throw statement?
- What is @@ERROR?
- How to get @@error and @@rowcount at the same time?
Why we need Exception Handling in SQL Server?
Let’s understand the need for Exception handling with an example. Let us create a stored procedure for dividing two numbers as shown below.
Execution:
EXEC DIVIDE 100, 5
EXEC DIVIDE 100, 0
In the above case when we execute the procedure we will get the error at the second execution because the divisor value is ZERO where a number cannot be divided by zero as per the rule of mathematics.
In SQL Server whenever an error occurs at a 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 the result will be as following
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.
Note: Whenever an error occurred while executing a program developed by using any programming language the program terminates abnormally on the line where the error got occurred but in SQL Server it will still continue the program execution.
What is Exception Handling?
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 to the error to execute but handling an error in SQL Server means stopping the execution of the statements which are related with the error.
With the introduction of Try/Catch blocks in SQL Server 2005, error handling in SQL Server is now similar to programming languages like C#, and java. Before understanding error handling using try/catch, let’s step back and understand how error handling was done in SQL Server 2000, using system function @@Error. Sometimes, system functions that begin with two at signs (@@), are called global variables. They are not variables and do not have the same behaviors as variables, instead, they are very similar to functions.
Now let’s create tblProduct and tblProductSales that we will be using for the rest of this demo.
--SQL script to create tblProduct
Create Table tblProduct
(
ProductId int NOT NULL primary key,
Name nvarchar(50),
UnitPrice int,
QtyAvailable int
)
Go
-- SQL script to load data into tblProduct
Insert into tblProduct values(1, 'Laptops', 2340, 100)
Insert into tblProduct values(2, 'Desktops', 3467, 50)
Go
-- SQL script to create tblProductSales
Create Table tblProductSales
(
ProductSalesId int primary key,
ProductId int,
QuantitySold int
)
Go
--Create a procedure
Create Procedure spSellProduct
@ProductId int,
@QuantityToSell int
As
Begin
-- Check the stock available, for the product we want to sell
Declare @StockAvailable int
Select @StockAvailable = QtyAvailable
from tblProduct where ProductId = @ProductId
-- Throw an error to the calling application, if enough stock is not available
If(@StockAvailable< @QuantityToSell)
Begin
Raiserror('Not enough stock available',16,1)
End
-- If enough stock available
Else
Begin
Begin Transaction
-- First reduce the quantity available
Update tblProduct set QtyAvailable = (QtyAvailable - @QuantityToSell)
where ProductId = @ProductId
Declare @MaxProductSalesId int
-- Calculate MAX ProductSalesId
Select @MaxProductSalesId = Case When
MAX(ProductSalesId) IS NULL
Then 0 else MAX(ProductSalesId) end
from tblProductSales
-- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
Set @MaxProductSalesId = @MaxProductSalesId + 1
Insert into tblProductSales values(@MaxProductSalesId, @ProductId, @QuantityToSell)
Commit Tran
End
End
Stored procedure – spSellProduct, has 2 parameters – @ProductId and @QuantityToSell. @ProductId specifies the product that we want to sell, and @QuantityToSell specifies the quantity we would like to sell.
In the procedure, we are using Raiserror() function to return an error message back to the calling application, if the stock available is less than the quantity we are trying to sell. We have to pass at least 3 parameters to the Raiserror() function.
RAISERROR(‘Error Message’, ErrorSeverity, ErrorState)
Severity and State are integers. In most cases, when you are returning custom errors, the severity level is 16, which indicates general errors that can be corrected by the user. In this case, the error can be corrected, by adjusting the @QuantityToSell, to be less than or equal to the stock available. ErrorState is also an integer between 1 and 255. RAISERROR only generates errors with the state from 1 through 127.
The problem with this procedure is that the transaction is always committed. Even, if there is an error somewhere, between updating tblProduct and tblProductSales table. In fact, the main purpose of wrapping these 2 statements (Update tblProduct Statement & Insert into tblProductSales statement) in a transaction is to ensure that, both of the statements are treated as a single unit. For example, if we have an error when executing the second statement, then the first statement should also be rolled back.
In SQL server 2000, to detect errors, we can use @@Error system function. @@Error returns a NON-ZERO value, if there is an error, otherwise ZERO, indicating that the previous SQL statement encountered no errors. The stored procedure spSellProductCorrected, makes use of @@ERROR system function to detect any errors that may have occurred. If there are errors, roll back the transaction, else commit the transaction. If you comment the line (Set @MaxProductSalesId = @MaxProductSalesId + 1) and then execute the stored procedure there will be a primary key violation error, when trying to insert into tblProductSales. As a result of this, the entire transaction will be rolled back.
Alter Procedure spSellProductCorrected
@ProductId int,
@QuantityToSell int
As
Begin
-- Check the stock available, for the product we want to sell
Declare @StockAvailable int
Select @StockAvailable = QtyAvailable
From tblProduct where ProductId = @ProductId
-- Throw an error to the calling application, if enough stock is not available
If(@StockAvailable< @QuantityToSell)
Begin
Raiserror('Not enough stock available',16,1)
End
-- If enough stock available
Else
Begin
Begin Tran
-- First reduce the quantity available
Update tblProduct set QtyAvailable = (QtyAvailable - @QuantityToSell)
where ProductId = @ProductId
Declare @MaxProductSalesId int
-- Calculate MAX ProductSalesId
Select @MaxProductSalesId = Case When
MAX(ProductSalesId) IS NULL
Then 0 else MAX(ProductSalesId) end
From tblProductSales
-- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
Set @MaxProductSalesId = @MaxProductSalesId + 1
Insert into tblProductSales values(@MaxProductSalesId, @ProductId, @QuantityToSell)
if(@@ERROR <> 0)
Begin
Rollback Tran
Print 'Rolled Back Transaction'
End
Else
Begin
Commit Tran
Print 'Committed Transaction'
End
End
End
Note:
@@ERROR is cleared and reset on each statement execution. Check it immediately following the statement being verified, or save it to a local variable that can be checked later.
In the tblProduct table, we already have a record with ProductId = 2. So the insert statement causes a primary key violation error. @@ERROR retains the error number, as we are checking for it immediately after the statement that causes the error.
Insert into tblProduct values(2, 'Mobile Phone', 1500, 100) if(@@ERROR <> 0) Print 'Error Occurred' Else Print 'No Errors'
On the other hand, when you execute the code below, you get a message ‘No Errors’ printed. This is because the @@ERROR is cleared and reset on each statement execution.
Insert into tblProduct values(2, 'Mobile Phone', 1500, 100) --At this point @@ERROR will have a NON ZERO value Select * from tblProduct --At this point, @@ERROR gets reset to ZERO, because of the --select statement successfully executed if(@@ERROR <> 0) Print 'Error Occurred' Else Print 'No Errors'
In this example, we are storing the value of the @@Error function to a local variable, which is then used later.
Declare @Error int Insert into tblProduct values(2, 'Mobile Phone', 1500, 100) Set @Error = @@ERROR Select * from tblProduct if(@Error <> 0) Print 'Error Occurred' Else Print 'No Errors'
Error handling in SQL Server 2005 and later versions:
Syntax:
BEGIN TRY
{ Any set of SQL statements }
END TRY
BEGIN CATCH
[ Optional: Any set of SQL statements ]
END CATCH
[Optional: Any other SQL Statements]
Any set of SQL statements, that can possibly throw an exception are wrapped between BEGIN TRY and END TRY blocks. If there is an exception in the TRY block, the control immediately jumps to the CATCH block. If there is no exception CATCH block will be skipped and the statements after the CATCH block are executed.
Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application the code in the CATCH block must do so by using RAISERROR() function.
In procedure spSellProduct, Begin Transaction and Commit Transaction statements are wrapped between Begin Try and End Try block. If there are no errors in the code that is enclosed in the TRY block, then COMMIT TRANSACTION gets executed and the changes are made permanent. On the other hand, if there is an error then the control immediately jumps to the CATCH block. In the CATCH block, we are rolling the transaction back. So it’s much easier to handle errors with Try/Catch construct than with @@Error system function.
Also notice that in the scope of the CATCH block, there are several system functions that are used to retrieve more information about the error that occurred these functions return NULL if they are executed outside the scope of the CATCH block.
TRY/CATCH cannot be used in a user-defined function.
Create Procedure spSellProduct
@ProductId int,
@QuantityToSell int
As
Begin
-- Check the stock available, for the product we want to sell
Declare @StockAvailable int
Select @StockAvailable = QtyAvailable
From tblProduct where ProductId = @ProductId
-- Throw an error to the calling application, if enough stock is not available
If(@StockAvailable< @QuantityToSell)
Begin
Raiserror('Not enough stock available',16,1)
End
-- If enough stock available
Else
Begin
Begin Try
Begin Transaction
-- First reduce the quantity available
Update tblProduct set QtyAvailable = (QtyAvailable - @QuantityToSell)
where ProductId = @ProductId
Declare @MaxProductSalesId int
-- Calculate MAX ProductSalesId
Select @MaxProductSalesId = Case When
MAX(ProductSalesId) IS NULL
Then 0 else MAX(ProductSalesId) end
From tblProductSales
--Increment @MaxProductSalesId by 1, so we don't get a primary key violation
Set @MaxProductSalesId = @MaxProductSalesId + 1
Insert into tblProductSales values(@MaxProductSalesId, @ProductId, @QuantityToSell)
Commit Transaction
End Try
Begin Catch
Rollback Transaction
Select
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_STATE() as ErrorState,
ERROR_SEVERITY() as ErrorSeverity,
ERROR_LINE() as ErrorLine
End Catch
End
End
How to handle errors in SQL Server?
From SQL Server 2005 we are provided with a structure error handling mechanism with the help of TRY and CATCH blocks which should be used as follows
Syntax:
BEGIN TRY
--statements which will cause the error
--statements which are related to the error that should not execute when the error occurred
END TRY
BEGIN CATCH
--statements which should be executed when the error occurs
END CATCH
To overcome the problem we faced in the dividing procedure rewrite the procedure as follows.
Example 1: Create a procedure for dividing 2 variables values by using TRY CATCH implementation with user-defined error statements.
CREATE PROCEDURE SPDIVIDE(@X INT, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z = 0
BEGIN TRY
SET @Z = @X / @Y
PRINT 'RESULT IS :- '+ CAST(@Z AS CHAR)
END TRY
BEGIN CATCH
PRINT 'SECOND NUMBER SHOULD NOT BE ZERO'
END CATCH
END
EXEC SPDIVIDE 10, 2
OUTPUT: RESULT IS:- 5
EXEC SPDIVIDE 10, 0
OUTPUT: SECOND NUMBER SHOULD NOT BE ZERO
When we execute with correct values error will not occur in the program so after executing all the statements in the try block control directly jumps to the statements present after catch block without executing the catch block.
If any error occurs in the execution process in such case from the line where the error got occurred in the try block, control directly jumps to the catch block. So rest of the statement in try will not execute whereas catch block will execute.
Note: In our above program when the error got occurred we are displaying an error message “SECOND NUMBER SHOULD NOT BE ZERO”. In place of that error message we can also display the original error message associated with that data by calling a function “Error_Message”. To test this rewriting the code inside the catch block as following
Print Error_Message()
Example 2: Create a procedure for dividing two variables values by using try catch implementation with system defined error statements.
CREATE PROCEDURE SPDIVIDE(@X INT, @Y INT)
AS
BEGIN
DECLARE @Z INT
BEGIN TRY
SET @Z = @X / @Y
PRINT 'RESULT IS :-' + CAST(@Z ASCHAR)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
END
EXEC SPDIVIDE 10, 0
OUTPUT: Divide by zero error encountered.
What is ERROR_MESSAGE()?
This method is used to display what type of error has occurred in the try block.
PREDEFINED ERRORS:
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
- State
Example: Message 8134 (Error Number), Level 16(SEVERITY Level), State 1 (state), Divide by Zero error encountered (Error Message)
ERROR NUMBER: Error number is a unique identification given for each and every error that occurs in the program. 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 maxed 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 from 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”
How to raise errors explicitly in a program?
Generally, errors raised in a program on predefined reasons like dividing a number by zero, violation of primary key, violation of check, violation of referential integrity etc. Whereas if required we can also raise an error in our programs in two different ways
- Using Raiserror statement
- Using a throw statement (new feature of SQL Server 2012)
Syntax: Raiserror
Raiserror (errorid/errormsg, SEVERITY, state)[with log]
Syntax: throw
Throw errorid, errormsg, state
What is the difference between the RAISERROR function and throw statement?
If we use any of the two statements in a program for raising an error without try and catch blocks, RAISERROR statement after raising the error will still continue the execution of the program whereas throw statement will terminate the program abnormally on that line. But if they are used under try block both will behave in the same way that is will jump directly to catch block from where the error got raised.
RAISERROR statement will give an option of specifying the SEVERITY of the error message whereas we don’t have these option in case of throw statement where all error message will have a default SEVERITY of 16.
In case of RAISERROR, there is a chance of recording the error message into the server log file by using the with log option whereas we cannot do this in case of throw statement.
In case of throw, we need to specify both errorid and error message to raise the error whereas in case of RAISERROR we can specify either id or message. If the id is not specified default error id is 50000 but if we want to specify only error id first we need to add the error message in the sysmessage table by specifying a unique id to the table.
Example: Write a Procedure for dividing two numbers and raise an error in the program if the division is 1 by using the RAISERROR statement.
CREATE PROCEDURE DIVIDEBYONE(@X INT, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z = 0
BEGIN TRY
IF @Y = 1
RAISERROR ('DIVISOR CANNOT BE ONE', 16, 1)
SET @Z = @X / @Y
PRINT'THE RESULT IS: '+CAST(@Z AS VARCHAR)
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH
END
EXEC DIVIDEBYONE 100, 1
The above procedure can also be defined with the help of a throw statement in place of Raiserror as following.
ALTER PROCEDURE DIVIDEBYONE(@X INT, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z = 0
BEGIN TRY
IF @Y = 1
THROW 50000,'DIVISOR CANNOT BE ONE', 1
SET @Z = @X / @Y
PRINT'THE RESULT IS: '+CAST(@Z AS VARCHAR)
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH
END
EXEC DIVIDEBYONE 100, 1
What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.
How to get @@error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in the same statement and store them in the local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
In the next article, I am going to discuss SQL Server Stored Procedure Interview Questions and Answers. Here, in this article, I try to explain most frequently asked SQL Server Exception Handling Interview Questions and Answers. I hope you enjoy this SQL Server Exception Handling Interview Questions and Answers article. I would like to have your feedback. Please post your feedback, question, or comments about this SQL Server Exception Handling Interview Questions and Answers article.
