Exception Dealing with in SQL Server ~ IT Tutorials with Instance

September 16, 2022

136

On this Article, we are going to study The way to Deal with Exception in SQL Server and in addition see The way to seize or Log the Exception in case of any DB Stage Exception happens in order that the Developer can seek advice from that Error log, can examine the severity of the Exception, and repair it with out losing an excessive amount of time find the exception inflicting process or perform or line which is inflicting the exception.

Let Start:

To be able to reveal how an exception is thrown within the process, I’ve created a Pattern Process i.e. usp_SampleProcedure as proven beneath

CREATE PROCEDURE dbo.[usp_SampleProcedure]

AS

BEGIN

SELECT
1 / 0 AS
End result;

END

Within the above process, I’ve written a question which is able to thow Divide by Zero Exception on the execution of the process.

Now with a purpose to deal with this exception, we have to use a try-catch block within the process. In case of exception, we are going to deal with or log the exception within the catch block. I’ve created a Desk i.e. DBErrorLogs with a purpose to seize the DB Stage Error. The schema of the desk is proven beneath.

Schema of Desk 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)

)

Process i.e. usp_SampleProcedure after utilizing Strive Catch Block:

CREATE PROCEDURE dbo.[usp_SampleProcedure]

AS

BEGIN

BEGIN TRY

SELECT
1 / 0 AS
End 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 may see, we’re capturing the varied fields with the assistance of predefined capabilities offered by SQL like UserName, ErrorNumber, ErrorState, ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMessage, ErrorOccuredOn, and so forth. which is able to assist us to seek out us the actual root inflicting situation of the Exception. Let’s perceive every perform rapidly.

1. SUSER_SNAME(): Returns the Login Identify for the present Safety Context.

2. ERROR_NUMBER(): Returns the error variety of the error which brought on the catch block of a try-catch assemble to execute. ERROR_NUMBER() returns NULL when known as outdoors of the scope of the Catch Block.

3. ERROR_STATE(): Returns the State Variety of the error message that brought on the Catch Block to Run and it returns NULL when known as outdoors of the scope of the Catch Block.

4. ERROR_SEVERITY(): returns the Severity of the Error when the error or exception happens. There are a number of ranges of Error Severity outlined by Microsoft which can be utilized to establish the kind of the issue encountered by the SQL Server. For extra, you may go to https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver15 hyperlink. ERROR_SEVERITY() returns NULL when known as outdoors of the scope of the Catch Block.

5. ERROR_LINE(): returns the road variety of the prevalence of the Error. ERROR_LINE returns NULL when known as outdoors of the scope of the Catch Block.

6. ERROR_PROCEDURE(): returns the identify of the process or set off on the prevalence of the Error. ERROR_PROCEDURE() returns NULL in case error didn’t happen within the saved process or set off or when known as outdoors of the scope of the Catch Block.

7. ERROR_MESSAGE(): returns the message textual content of the error that brought on the catch block of a try-catch block to execute.

The outcome on dealing with the exception with a try-catch block:

Messages which exhibits the data is inserted in DBErrorLogs Desk:

Preview of DBErrorLogs Desk:

Rollback Transaction In case of any Error/Exception:

Now let see find out how to rollback a transaction when an error or exception is encountered. For the demonstration, I’m utilizing the AdventureWorks Database to point out the beneath demo. I’m attempting to delete a document in a transaction that can’t be deleted due to the battle with the reference constraint and throws an error.

CREATE PROCEDURE dbo.[usp_SampleProcedure]

AS

BEGIN

BEGIN TRY

BEGIN
TRANSACTION

–For
Demo, delete question written beneath will throw error

–The
DELETE assertion conflicted with the REFERENCE constraint

DELETE

FROM
Individual.Individual

WHERE
BusinessEntityID = 20777

COMMIT
TRANSACTION

END TRY

BEGIN CATCH

–@@TRANCOUNT
for variety of BEGIN TRANSACTION statements

–that
have occurred on the present 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

Within the catch block, we’re checking @@TRANCOUNT with a purpose to examine whether or not any transaction is encountered earlier than throwing the error. If @@TRANCOUNT returns a price greater than 0, in that case, we are going to roll again the transaction and log the error particulars in our DBErrorLogs desk.

Preview: