Post
Ask Question

T/SQL - Exception Handling - Part Five

In this article, we will learn about exception handling in SQL Server. This is the Fifth Part of T/SQL series. You can check the previous articles of this series from these links.
Introduction 

Exception Handling in SQL Server: Exception Handling is used to handle errors in a program. We handle errors in a program both through the programming language as well as databases. Exception handling provides a proper way to handle an error. In programming, handling error needs to stop the abnormal termination and allows the statement which is not related to the error to execute. In the SQL Server, handling errors means stopping the execution of statements which is related to the error.pasting.error handling in SQL Server and provides more control over T/SQL.

Handle Error In SQL Server

SQL Server in 2005 added the structure of handling error with the help of try and catch blocks which should be used as follows-

SYNTAX
  1. Begin Try  
  2. <statements>  
  3. End Try   
  4. Begin Catch   
  5. <Statements>  
  6. End Catch  

Example

Create a procedure for dividing the two variable values using try and catch implementation with user defined error statement?

  1. create procedure sp_Add  
  2. @a int,@b int  
  3. as  
  4. begin  
  5. declare @c int  
  6. begin try  
  7. set @c=@a/@b  
  8. print 'Divide is'+ cast(@c as char)  
  9. end try  
  10. begin catch   
  11. print 'Diivde By Zero error'  
  12. end catch  
  13. end  
Now, execute the procedure.
  1. exec sp_add 4,0  
Result
 
Exception Handling in SQL Server 
 
Exception handling property in SQL Server, 
  • ERROR_MESSAGE()
  • ERROR_LINE()
  • ERROR_NUMBER()
  • ERROR_STATE()
  • ERROR_SEVERITY()
  • ERROR_PROCEDURE()

ERROR_MESSAGE

It is a predefined method which will be used for displaying the information about the execution which was found in the try block.

Example
 
Create a procedure for dividing two variable values by using try catch implementation with system defined error message ?
  1. create procedure sp_addone  
  2. @a int,  
  3. @b int  
  4. as  
  5. begin  
  6. declare @c int  
  7. begin try  
  8. set @c=@a/@b;  
  9. print 'Add is:'+cast (@C as char)  
  10. end try  
  11. begin catch  
  12. print error_message()  
  13. end catch  
  14. end  
Now, execute the procedure,
  1. exec sp_addone 2,0  

Result

 Exception Handling in SQL Server 

ERROR_Number

It is a pre-defined method which is used to display the number assigned to the error,

  1. create procedure sp_errornumber   
  2. @a int,    
  3. @b int    
  4. as    
  5. begin    
  6. declare @c int    
  7. begin try    
  8. set @c=@a/@b;    
  9. print 'Add is:'+cast (@C as char)    
  10. end try    
  11. begin catch    
  12. print error_number()    
  13. end catch    
  14. end    

Now, execute this Procedure,

Exception Handling in SQL Server 

ERROR_LINE

It is a pre-defined method which will used for find the line in which error found, 

  1. create procedure ERRORLINE   
  2. @a int,    
  3. @b int    
  4. as    
  5. begin    
  6. declare @c int    
  7. begin try    
  8. set @c=@a/@b;    
  9. print 'Add is:'+cast (@C as char)    
  10. end try    
  11. begin catch    
  12. print ERROR_LINE()    
  13. end catch    
  14. end    
Now execute the Procedure,
 
Result
 
Exception Handling in SQL Server

ERROR_STATE
 
This find error’s state number.
 
ERROR_PROCEDURe
 
Error_Procedure returns the name of the stored procedure or trigger where the error occurred.
 
We can check system defined error by using,
  1. select * from sys.messages  
Exception Handling in SQL Server 
 
Summary

In this article, we have learned about exception handling in SQL Server. This article is the last article of T/SQL Series. Now, we will start learning MongoDB. I have to prepare some article on MongoDB soon, for posting the complete series on NoSQL Database.


Similar Articles