How To Use TRY CATCH In SQL Procedure

Hardik Deshani

7 Comments

Join the conversation! Your thoughts help the community grow.

Hardik Deshani8y ago
Sorry i forgot that code dbo.GetDateTimeZone() this is one function that return DateTime as per timezone wise you can use GETDATE() instead of this function.
- Hardik Deshani8y ago
  Hi, I think you need to add ProcedureName but see my post screenshot there is one field "ErrorProcedure" is the name of the Procedure so you don't need to add this
- Jean-Patrick8y ago
  I have multiple stored procedure with the same name but in different schema, ErrorProcedure does not return schema name, so I did not found an other solution to differentiate wich one throw an error.
- Jean-Patrick8y ago
  Ok, that's what i have done. in addition, I choose to add the schemaName column in the error table, by providing the objectId via @@PROCID parameter and then call OBJECT_SCHEMA_NAME(@objectId) when inserting. So that i can know which stored procedure throw an exeception
Jean-Patrick8y ago
What is the code of the dbo.GetDateTimeZone() function ?
Rajesh Kumar8y ago
Nice article thanks for sharing.......................
Hadshana Kamalanathan8y ago
Thanks for sharing...

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.