Exception Handling in SQL Server

In this Article, we will learn How to Handle Exception in SQL Server and also see How to capture or Log the Exception in case of any DB Level Exception occurs so that the Developer can refer to that Error log, can check the severity of the Exception, and fix it without wasting too much time in finding the exception causing procedure or function or line which is causing the exception.

Let Begin:

In order to demonstrate how an exception is thrown in the procedure, I have created a Sample Procedure i.e. usp_SampleProcedure as shown below

CREATE PROCEDURE dbo.[usp_SampleProcedure] AS BEGIN        SELECT 1 / 0 AS Result; END

In the above procedure, I have written a query which will thow Divide by Zero Exception on the execution of the procedure.

Now in order to handle this exception, we need to use a try-catch block in the procedure. In case of exception, we will handle or log the exception in the catch block. I have created a Table i.e. DBErrorLogs in order to capture the DB Level Error. The schema of the table is shown below.

Schema of Table DBErrorLogs:

CREATE TABLE dbo.DBErrorLogs        (        ErrorLogID     BIGINT IDENTITY NOT NULL,        UserName       NVARCHAR (200) NOT NULL,        ErrorNumber    INT NOT NULL,        ErrorState     INT NOT NULL,        ErrorSeverity  INT NOT NULL,        ErrorLine      INT NOT NULL,        ErrorProcedure VARCHAR (max) NOT NULL,        ErrorMessage   VARCHAR (max) NOT NULL,        ErrorOccuredOn DATETIME NOT NULL,        CONSTRAINT PK_DBErrorLogs PRIMARY KEY (ErrorLogID)        )

Procedure i.e. usp_SampleProcedure after using Try Catch Block:

CREATE PROCEDURE dbo.[usp_SampleProcedure] AS BEGIN        BEGIN TRY               SELECT 1 / 0 AS Result;        END TRY          BEGIN CATCH               INSERT INTO dbo.dbErrorLogs (                      UserName                      ,ErrorNumber                      ,ErrorState                      ,ErrorSeverity                      ,ErrorLine                      ,ErrorProcedure                      ,ErrorMessage                      ,ErrorOccuredOn                      )               VALUES (                      SUSER_SNAME()                      ,ERROR_NUMBER()                      ,ERROR_STATE()                      ,ERROR_SEVERITY()                      ,ERROR_LINE()                      ,ERROR_PROCEDURE()                      ,ERROR_MESSAGE()                      ,GETDATE()                      )        END CATCH END

As you can see, we are capturing the various fields with the help of predefined functions provided by SQL like UserName, ErrorNumber, ErrorState, ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMessage, ErrorOccuredOn, etc. which will help us to find us the real root causing issue of the Exception. Let’s understand each function quickly.

1. SUSER_SNAME(): Returns the Login Name for the current Security Context. 

2. ERROR_NUMBER(): Returns the error number of the error which caused the catch block of a try-catch construct to execute. ERROR_NUMBER() returns NULL when called outside of the scope of the Catch Block.

3. ERROR_STATE(): Returns the State Number of the error message that caused the Catch Block to Run and it returns NULL when called outside of the scope of the Catch Block.

4. ERROR_SEVERITY(): returns the Severity of the Error when the error or exception occurs. There are several levels of Error Severity defined by Microsoft which can be used to identify the type of the problem encountered by the SQL Server. For more, you can visit https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver15 link. ERROR_SEVERITY() returns NULL when called outside of the scope of the Catch Block.

5. ERROR_LINE(): returns the line number of the occurrence of the Error. ERROR_LINE returns NULL when called outside of the scope of the Catch Block.

6. ERROR_PROCEDURE(): returns the name of the procedure or trigger on the occurrence of the Error. ERROR_PROCEDURE() returns NULL in case error did not occur in the stored procedure or trigger or when called outside of the scope of the Catch Block.

7. ERROR_MESSAGE(): returns the message text of the error that caused the catch block of a try-catch block to execute.

The result on handling the exception with a try-catch block:

Messages which shows the records is inserted in DBErrorLogs Table:

Preview of DBErrorLogs Table:

Rollback Transaction In case of any Error/Exception:

Now let see how to rollback a transaction when an error or exception is encountered. For the demonstration, I am using the AdventureWorks Database to show the below demo. I am trying to delete a record in a transaction that cannot be deleted because of the conflict with the reference constraint and throws an error.

CREATE PROCEDURE dbo.[usp_SampleProcedure] AS BEGIN        BEGIN TRY               BEGIN TRANSACTION                 --For Demo, delete query written below will throw error               --The DELETE statement conflicted with the REFERENCE constraint               DELETE               FROM Person.Person               WHERE BusinessEntityID = 20777                 COMMIT TRANSACTION        END TRY          BEGIN CATCH               --@@TRANCOUNT for number of BEGIN TRANSACTION statements               --that have occurred on the current connection.               IF @@TRANCOUNT > 0                      ROLLBACK TRANSACTION;                 INSERT INTO dbo.dbErrorLogs (                      UserName                      ,ErrorNumber                      ,ErrorState                      ,ErrorSeverity                      ,ErrorLine                      ,ErrorProcedure                      ,ErrorMessage                      ,ErrorOccuredOn                      )               VALUES (                      SUSER_SNAME()                      ,ERROR_NUMBER()                      ,ERROR_STATE()                      ,ERROR_SEVERITY()                      ,ERROR_LINE()                      ,ERROR_PROCEDURE()                      ,ERROR_MESSAGE()                      ,GETDATE()                      )        END CATCH END

In the catch block, we are checking @@TRANCOUNT in order to check whether any transaction is encountered before throwing the error. If @@TRANCOUNT returns a value more than 0, in that case, we will roll back the transaction and log the error details in our DBErrorLogs table.

Preview:

I hope this article helps you in getting a basic understanding to handle exceptions or errors in the SQL Server.