How to do error/exception handling in SQL Server

Hello friends, today will learn how to perform error/exception handling in SQL Server. In earlier versions (below SQL 2005), error handling was not an easy task in SQL Server. We had to write error handling code for each T-SQL statement individually, for example if we have 10 T-SQL statements in a stored procedure, we have to write 10 more lines of code to handle the errors. But Microsoft has introduced a strong and efficient error handling feature in SQL Server 2005 and later versions. Let see how to handle errors/exceptions in SQL Server 2005 and later versions.

Lets create a stored procedure for division of two numbers with error handling code using below SQL script.

Alter procedure Usp_Division_of_Numbers(@numerator int,@denominator int)
as
Begin
Begin try
      Declare @result as float
      set @result =@numerator /@denominator
      select @result
End try
begin Catch
      select ERROR_LINE() as [Error Line],ERROR_NUMBER() as [Error No],
      ERROR_MESSAGE() as [Error Msg],ERROR_PROCEDURE() as [Error Procedure]
end Catch
end

If you look at the above SQL script, you can see there are two blocks defined in the stored procedure which is explained below.

Begin try
      Declare @result as float
      set @result =@numerator /@denominator
      select @result
End try

We put all the T-SQL code (which is error prone) in between Begin try and End try statements.

begin Catch
      select ERROR_LINE() as [Error Line],ERROR_NUMBER() as [Error No],
      ERROR_MESSAGE() as [Error Msg],ERROR_PROCEDURE() as [Error Procedure]
end Catch

We put all the error handling code in between Begin Catch and End Catch statements.


If any error occurred during the execution of stored procedure control goes to the first statement of the Catch Block and execute all the code written in the catch block. If execution of stored procedure does not encounter any error or exception, Catch block gets ignored and will not be executed. Lets execute the stored procedure and see the results.

Execute Usp_Division_of_Numbers 10,2


In the above screenshot, we got the result as number 5, which is correct. If we divide number 10 with 2, we will get 5. Lets create a scenario where we should get an error, hence I am passing 0 as denominator so that we should encounter an error.


In the above screenshot, we can see an error has occurred and we received “divide by zero error encountered” message.

Error Handling in earlier versions(<SQL2005):- Lets have a look on implementation of error handling in earlier versions.

Create procedure Usp_Division_of_Numbers(@numerator int,@denominator int,@Error int Out)
as
Begin
      Declare @result as float
      set @result =@numerator /@denominator
      set @error=@@ERROR
      if @error <>0 goto Errhandler
      select @result
      set @error=@@ERROR
      if @error <>0 goto Errhandler
      Errhandler:
      return @error
end

You can see in the above SQL script, we are setting the @error=@@Error after each T-SQL statement and checking its value <>0.  If an error occurs @error value will not be zero. Lets execute the stored procedure and see how it works.


We can see in the above screenshot, we are getting error number 8134 for divide by zero error. I hope after reading this article, you will be able to do error handling in your SQL code efficiently. Please feel free to ask any question related to this article and share your feedback.

No comments: