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.