Stored Procedure Error Logging

It's time to get outdoors and Explore

This is simple stored procedure to capture Errors raised during TRY/CATCH Block. This Logs the errors in a Table which can be used for troubleshooting and Trending Analysis


Create table to capture Errors
Create Stored Procedure to Log Error
Invoke Stored Procedure in TRY/CATCH Block

 

USE TEMPDB;
GO
IF OBJECT_ID('APM.ErrorLog') IS NOT NULL
DROP TABLE APM.ErrorLog;
GO

CREATE TABLE APM.ErrorLog
(
ErrorLogID INT IDENTITY(1, 1) PRIMARY KEY,
DatabaseID INT,
DatabaseName sysname,
SPID INT,
ErrorNumber INT,
ErrorSeverity INT,
ErrorState INT,
ErrorProcedure VARCHAR(400),
ErrorMessage NVARCHAR(MAX),
AdditionalInfo NVARCHAR(MAX),
CreatedBY VARCHAR(50)
DEFAULT SYSTEM_USER,
CreatedDateTime DATETIME
DEFAULT GETUTCDATE(),
Status INT
DEFAULT 1
);
GO

CREATE OR ALTER PROC APM.LogErrorInfo
(
@pAdditionalIndfo NVARCHAR(550) = NULL,
@pCreatedBy VARCHAR(50) = NULL
)
AS
BEGIN
INSERT INTO APM.ErrorLog
(
DatabaseID,
DatabaseName,
SPID,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorMessage,
AdditionalInfo,
CreatedBY
)
SELECT DB_ID(), -- DatabaseID - int
DB_NAME(), -- DatabaseName - sysname
@@SPID, -- SPID - int
ERROR_LINE(), -- ErrorNumber - int
ERROR_SEVERITY(), -- ErrorSeverity - int
ERROR_STATE(), -- ErrorState - int
ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400)
ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max)
ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)
ISNULL(@pCreatedBy, SYSTEM_USER);

SELECT DB_ID(), -- DatabaseID - int
DB_NAME(), -- DatabaseName - sysname
@@SPID, -- SPID - int
ERROR_LINE(), -- ErrorNumber - int
ERROR_SEVERITY(), -- ErrorSeverity - int
ERROR_STATE(), -- ErrorState - int
ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400)
ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max)
ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)
ISNULL(@pCreatedBy, SYSTEM_USER);

/*
-- UNIT TEST
BEGIN TRY
SELECT 1 /0
END TRY
BEGIN CATCH
EXEC APM.LogErrorInfo @pAdditionalIndfo ='Unit Test'
END CATCH

SELECT * FROM APM.ErrorLog
*/

END;

Posted in Microsoft Technologies, SQL Server on Oct 28, 2017


Comments

Please sign in to comment!