October 10, 2018 11:18 by
Peter
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.
European SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.