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:
Post a Comment