T/SQL - Exception Handling

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

Begin Try
<statements>
End Try
Begin Catch
<Statements>
End Catch

Example

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

create procedure sp_Add
@a int,@b int
as
begin
declare @c int
begin try
set @c=@a/@b
print 'Divide is'+ cast(@c as char)
end try
begin catch
print 'Diivde By Zero error'
end catch
end

Now, execute the procedure.

exec sp_add 4,0

Result

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 ?

create procedure sp_addone
@a int,
@b int
as
begin
declare @c int
begin try
set @c=@a/@b;
print 'Add is:'+cast (@C as char)
end try
begin catch
print error_message()
end catch
end

Now, execute the procedure,

exec sp_addone 2,0

Result

ERROR_Number

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

create procedure sp_errornumber
@a int,
@b int
as
begin
declare @c int
begin try
set @c=@a/@b;
print 'Add is:'+cast (@C as char)
end try
begin catch
print error_number()
end catch
end

Now, execute this Procedure,

ERROR_LINE

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

create procedure ERRORLINE
@a int,
@b int
as
begin
declare @c int
begin try
set @c=@a/@b;
print 'Add is:'+cast (@C as char)
end try
begin catch
print ERROR_LINE()
end catch
end

Now execute the Procedure,

Result

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,

select * from sys.messages

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.