How To Use TRY CATCH In SQL Procedure

In this post, we will learn about how to use TRY CATCH in SQL Procedure and store error with error text and procedure with the line number in the table. Here is a simple example for generating the error and store in SQL table. Let's start coding.

In this post, we will learn how to use TRY CATCH in SQL procedure and store an error with error text. Here is a simple example for generating the error and storing it in a SQL table. Let's start coding.

For saving the error in the table first we need to create the table in SQL Database. See below.

CREATE TABLE [dbo].[Error_StoreProcedure](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ErrorNumber] [varchar](50) NULL,
    [ErrorSeverity] [varchar](50) NULL,
    [ErrorState] [varchar](50) NULL,
    [ErrorProcedure] [varchar](500) NULL,
    [ErrorLine] [varchar](50) NULL,
    [ErrorMessage] [varchar](max) NULL,
    [EntryDate] [datetime] NULL,
 CONSTRAINT [PK_Error_StoreProcedure] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

After creating the above table we need to create one procedure for saving the error in the table;  see below.

CREATE PROCEDURE usp_GetErrorInfo
AS
BEGIN
    INSERT INTO Error_StoreProcedure SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage
    ,dbo.GetDateTimeZone()
END

 After creating the above procedure now we have to use the above procedure inside the other procedure.

CREATE PROCEDURE TESTING_ERROR_PROCEDURE

AS
BEGIN
 SET NOCOUNT ON;

    BEGIN TRY

        -- Generate divide-by-zero error.
        SELECT 1/0;

    END TRY
    BEGIN CATCH

        -- Execute error retrieval routine.
        EXECUTE usp_GetErrorInfo;

    END CATCH;

END
GO

The above procedure generates the error and goes to the CATCH part and saves all information of the error into our error table.

Run this query SELECT * FROM Error_StoreProcedure

See the output of the above table. Output displays procedure name and line number of the error.