Generic stored procedures logging functionality

Question

Hi,

I need to create stored procedure to store logging information into table,

as I have one wrapper procedure which is calling 10 other procedure and hence to track which proc got failed and when and why.

I tried using

ERROR_NUMBER()
,ERROR_MESSAGE()
,ERROR_PROCEDURE()
,ERROR_STATE()
,ERROR_SEVERITY()
,ERROR_LINE()

but it is not generic

Thank you

Sachin Singh · Answer

steps GO CREATE TABLE dbo.ProcedureLog ( LogDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, DatabaseID INT, ObjectID INT, ProcedureName NVARCHAR(400), ErrorLine INT, ErrorMessage NVARCHAR(MAX), AdditionalInfo NVARCHAR(MAX) ); GO CREATE CLUSTERED INDEX cx_LogDate ON dbo.ProcedureLog(LogDate); GO USE ; GO CREATE SCHEMA utility AUTHORIZATION dbo; GO CREATE PROCEDURE utility.Log_ProcedureCall @ObjectID INT, @DatabaseID INT = NULL, @AdditionalInfo NVARCHAR(MAX) = NULL AS BEGIN SET NOCOUNT ON; DECLARE @ProcedureName NVARCHAR(400); SELECT @DatabaseID = COALESCE(@DatabaseID, DB_ID()), @ProcedureName = COALESCE ( QUOTENAME(DB_NAME(@DatabaseID)) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectID, @DatabaseID)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectID, @DatabaseID)), ERROR_PROCEDURE() ); INSERT Utility.dbo.ProcedureLog ( DatabaseID, ObjectID, ProcedureName, ErrorLine, ErrorMessage, AdditionalInfo ) SELECT @DatabaseID, @ObjectID, @ProcedureName, ERROR_LINE(), ERROR_MESSAGE(), @AdditionalInfo; END GO use the utility.Log_ProcedureCall in yr procedure ALTER PROCEDURE yrSP @Name NVARCHAR(64) AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM mytable WHERE name = @Name) BEGIN DECLARE @msg NVARCHAR(MAX); SET @msg = 'Something went horribly wrong.' ; EXEC utility.Log_ProcedureCall @ObjectID = @@PROCID, @AdditionalInfo = @msg; RAISERROR(@msg, 11, 1); RETURN; END END GO

Jignesh Kumar · Answer

Hi Jayu, Please use this one, https://www.c-sharpcorner.com/UploadFile/858292/sql-server-error-logging-from-a-stored-procedure/