Error Handling in SQL Server

Introduction: When we develop a project or an application, our main intention is always that our application should be error free. Sometimes we get an exception and error at runtime after deploying the project and to remove those errors we also use Error Handling. In the same manner we also take care with error handling in our database and in our Stored Procedure.

 

Generally most developers try to handle all types of errors in programming code and they use try and catch blocks to handle errors, but it may be possible that an error can occur inside the database so to remove these types of error we have a very good concept of error handling in our SQL Server database. To remove these types of errors inside from our Stored Procedure we can also use error handling for our database.

 

How to handle errors in SQL

There  are so many ways to handle your exception but the most common are only the following two:

1. Using Try and Catch
2. Using @@ERROR global variable

1 Using Try and Catch: Generally this method is used because this mechanism is common with so many languages. Inside this try block we always write that code where we may get errors. If any error occurs in the try block then the control will be transfered to the catch block, where we can:

• Handle our Exceptions 
• Maintain Error Logs
• Rollback our transaction

 

 Syntex 

begin try
--SQL Statement
--SQL Statement
..............
--SQL Statement
end try

begin catch
--SQL Statement
--SQL Statement
..............
--SQL Statement
end catch

Example:

begin try
declare @var1 int;  --declare var1 varable
declare @var2 int;  --declare var2 variable
declare @Divide int; --declare Divide variable
set @var1=50; --assigning var1 to 50
set @var2=0; --assigning var2 to 0
set @Divide=@var1/@var2; --performing var1 / var2 (50 / 0)
end try

begin catch
select 'Sorry Error Occur' as Error
end catch

By using the code above we will get output like:

 

 

There are some functions also available by which you can see the complete details of the error. So sometimes what developers do is that if an error occurs then they maintain logs and a temporary table that they create where they store all the information about the error.

 

Built-in Error Functions in SQL

Example:

 

begin try
select 1/0 --Error Occur
end try

begin catch
select
ERROR_MESSAGE() as ErrorMessage,
ERROR_LINE() as ErrorLine,
ERROR_NUMBER() as ErrorNumber,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_SEVERITY() as ErrorSeverity,
ERROR_STATE() as ErrorState
end catch

 

 

2. Using @@ERROR global variable: @@Error is the global variable by which we can also handle our error. This global variable gives the error message when an error occurs and if an error occurs then it gives an error number.

 

Example: 

select 1/0;
Declare @Err_num int; --Declare variable
set @Err_Num=@@ERROR; --assigning error number to variable @Err_Num
select @Err_num as Error_Number

After executing this you will get the following output:

 

And as a result you will get the following: