Post
Ask Question

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 
  1. begin try  
  2. --SQL Statement  
  3. --SQL Statement  
  4. ..............  
  5. --SQL Statement  
  6. end try  
  7.   
  8. begin catch  
  9. --SQL Statement  
  10. --SQL Statement  
  11. ..............  
  12. --SQL Statement  
  13. end catch  
Example:
  1. begin try   
  2. declare @var1 int;  --declare var1 varable  
  3. declare @var2 int;  --declare var2 variable  
  4. declare @Divide int; --declare Divide variable  
  5. set @var1=50; --assigning var1 to 50  
  6. set @var2=0; --assigning var2 to 0  
  7. set @Divide=@var1/@var2; --performing var1 / var2 (50 / 0)   
  8. end try  
  9.   
  10. begin catch  
  11. select 'Sorry Error Occur' as Error  
  12. 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:
 
  1. begin try   
  2. select 1/0 --Error Occur  
  3. end try  
  4.   
  5. begin catch  
  6. select   
  7. ERROR_MESSAGE() as ErrorMessage,  
  8. ERROR_LINE() as ErrorLine,   
  9. ERROR_NUMBER() as ErrorNumber,  
  10. ERROR_PROCEDURE() as ErrorProcedure,  
  11. ERROR_SEVERITY() as ErrorSeverity,  
  12. ERROR_STATE() as ErrorState  
  13. 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: 
  1. select 1/0;   
  2. Declare @Err_num int; --Declare variable  
  3. set @Err_Num=@@ERROR; --assigning error number to variable @Err_Num  
  4. select @Err_num as Error_Number   
After executing this you will get the following output:

 
And as a result you will get the following:
 
 
 


Similar Articles