Error Handling in SQL Server

Sourabh Somani
10y
4.4k
0
6

Article

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:

Using Try and Catch
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: