Generic stored procedures logging functionality

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

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar4y ago
Hi Jayu,

Please use this one,
https://www.c-sharpcorner.com/UploadFile/858292/sql-server-error-logging-from-a-stored-procedure/
Sachin Singh4y ago
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