Generic stored procedure to track the errors in a table

Purpose of the stored procedure is to create a generic stored procedure which will capture the errors whenever error has been occurred

 

Table : 　

CREATE TABLE [dbo].[SQLErrorLog]

([SQLErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[DbID] [int] NULL,
[Application] [varchar](128) NOT NULL,
[Host] [varchar](128) NOT NULL,
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [varchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [varchar](2048) NULL,
[MetaCreateTS] [datetime2](7) NOT NULL,
[MetaCreateServerName] [varchar](50) NOT NULL,
[MetaCreateSUserName] [varchar](50) NOT NULL,
CONSTRAINT [PK_SQLErrorLog] PRIMARY KEY CLUSTERED(
[SQLErrorLogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 Stored Procedure :

CREATE PROCEDURE usp_SQLErrorLog
@Application varchar(100)
AS

BEGIN
SET NOCOUNT ON;

BEGIN TRY

INSERT INTO [dbo].[SQLErrorLog]

([DbID]
,[Application]
,[Host]
,[ErrorNumber]
,[ErrorSeverity]
,[ErrorState]
,[ErrorProcedure]

,[ErrorLine]
,[ErrorMessage]
,[MetaCreateTS]
,[MetaCreateServerName]
,[MetaCreateSUserName])

SELECT DB_ID(),
@Application,
HOST_NAME(),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
GETDATE() ,
@@servername ,
SUSER_NAME()
END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXEC xp_logevent 60000, @Application, informational;

END CATCH;

END

 

 Testing :

BEGIN TRY

-- Generate divide-by-zero error.

SELECT 1/0;

END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXECUTE usp_SQLErrorLog ‘Application’

END CATCH;