Exception Handling In SQL Server

If you are aware of any programming language, then you must know about exception handling like “Try, Catch” blocks. This is used in SQL Servers as well.

 

Firstly, SQL Server 2005 introduced Try-Catch blocks to handle errors effectively from the backend side.

 

The handling of exceptions can provide more and specific information about errors and translate those errors into some meaningful message to make the user understand by logging and saving to the table.

 

Now, we will understand in depth how it works.

 

Syntax

BEGIN TRY
//Your Logic
END TRY
BEGIN CATCH
//if an exception came in try block you can handle here
END  CATCH

Your logic should come inside the BEGIN TRY and END TRY . If during any transaction in any line inside the try block an exception is raised, then it will send to CATCH block .

Inside the Catch block you will have that exception. You can do anything with that exception like you can log the error or report that error or you can throw that exception to the front end user with any meaningful statement. In this way we can try to fix raised exceptions.

 

We can use some special in-built functions inside the catch block, Let's have a look.

• ERROR_NUMBER() - returns the internal number of the error.
• ERROR_STATE() - returns the information about the source.
• ERROR_SEVERITY() - returns the severity of error that occurred. We can call anywhere but it returns severity if it is in the catch block else it returns NULL.
• ERROR_PROCEDURE() - returns the Name of the Procedure.
• ERROR_LINE() - returns the line number where the error occurred.
• ERROR_MESSAGE() - returns the exact error message.

This information is very useful when you are doing big projects like banking, ticket booking apps, etc. When we are working on such projects, there comes the COMMIT and ROLLBACK keywords into the picture.

 

For eg: We have to write COMMIT in the try block and ROLLBACK into the Catch block. Suppose a transaction is going on and in the middle some error occurs --  then it will go to Catch block and ROLLBACK the whole Transaction and save the error related log. If the transaction is complete we can COMMIT.

 

 

As in the above Image, we are doing divideByZero which will return an error and the Catch block is executed.

 

From the Catch block, we get the error-related information. As we see ErrorProcedure is getting NULL because we are not writing all this into a Stored Procedure.

 

We got two tables as a result because we have written two select statements, one in the Try block and another in the Catch block.

Now we will have one more example with Stored Procedure and make a log of the Error,

 

Create a Table like below,

CREATE TABLE DB_Errors_Logs
(
   ID                       INT IDENTITY(1, 1),
   UserName                 VARCHAR(MAX),
   ErrorNumber              INT,
   ErrorState               INT,
   ErrorSeverity            INT,
   ErrorLine                INT,
   ErrorProcedure           VARCHAR(MAX),
   ErrorMessage             VARCHAR(MAX),
   EmployeeName             VARCHAR(100),
   CreatedBy                INT,
   ErrorCreatedDateTime     DATETIME
)

Here is the Stored Procedure for inserting the employee,

ALTER PROCEDURE [dbo].[InsertEmployees]
(
   @EmployeeName VARCHAR(100),
   @Gender char(1),
   @Emailid nvarchar(100)=null,
   @Age int =null,
   @Phoneno NVARCHAR(10)=null,
   @Departmentid int,
   @CreatedBy int
)
AS
BEGIN
BEGIN TRY
begin tran
insert into employee(EmpName,Gender,Emailid,Age,Phoneno,Departmentid )
values(@EmployeeName,@Gender,@Emailid,@Age,@Phoneno,@Departmentid)
commit
END TRY
BEGIN CATCH
rollback
INSERT INTO dbo.DB_Errors_Logs(UserName,ErrorNumber,ErrorState,ErrorSeverity,ErrorLine,ErrorProcedure,ErrorMessage,EmployeeName,CreatedBy,ErrorCreatedDateTime)
VALUES (
   SUSER_SNAME(),
   ERROR_NUMBER(),
   ERROR_STATE(),
   ERROR_SEVERITY(),
   ERROR_LINE(),
   ERROR_PROCEDURE(),
   ERROR_MESSAGE(),
   @EmployeeName,
   @CreatedBy,
   GETDATE()
)
END CATCH
END
GO

In the above image, we have inserted the record successfully, So it is displaying as 1 row affected.

 

 

There we got two results,

1. 0 rows affected - Nothing is inserted in the Employee table.
2. 1 row affected - error information is inserted in the DB_Errors_Logs table.

In the next article, we will learn advanced exception handling in SQL.