Simple message logging

This article describes an approach to one-line, simple event logging from within stored procedures; something like this:

EXEC dbo.usp_LogEvt 'Here is a message', @@PROCID

1. Create a log table in a convenient administration database. Mine is called _Admin but the name isn't important – if you already have one you can use that instead.

   USE _Admin
   GO
    
   IF OBJECT_ID('dbo.EventLog') IS NOT NULL
     DROP TABLE dbo.EventLog
    
   GO
    
   CREATE TABLE dbo.EventLog (
     EventId INT IDENTITY(1,1) PRIMARY KEY
   , EventTimestamp DATETIME NOT NULL DEFAULT GETDATE()
   , SourceDb VARCHAR(128) NOT NULL
   , SourceSpSchema VARCHAR(128)
   , SourceSpName VARCHAR(128)
   , [Message] VARCHAR(MAX) NOT NULL
   )

2. Create an SP in the same database to write entries into the log

   USE _Admin
   GO
    
   IF OBJECT_ID('dbo.usp_MakeLogEntry') IS NOT NULL
     DROP PROCEDURE dbo.usp_MakeLogEntry
    
   GO
    
   CREATE PROCEDURE dbo.usp_MakeLogEntry (
     @srcDb VARCHAR(128)
   , @srcSpSchema VARCHAR(128)
   , @srcSpName VARCHAR(128)
   , @msg VARCHAR(MAX)
   )
   AS
   BEGIN
    
     INSERT INTO dbo.EventLog (
       SourceDb
     , SourceSpSchema
     , SourceSpName
     , [Message]
     ) VALUES (
       @srcDb
     , @srcSpSchema
     , @srcSpName
     , @msg 
     )
    
     RETURN SCOPE_IDENTITY()
    
   END

3. usp_MakeLogEntry is not intended to be called directly – create this wrapper SP:

   IF OBJECT_ID('dbo.usp_LogEvt') IS NOT NULL
     DROP PROCEDURE dbo.usp_LogEvt
    
   GO
    
   CREATE PROCEDURE dbo.usp_LogEvt (
     @msg VARCHAR(MAX)
   , @objId INT = NULL
   )
   AS
   BEGIN
    
     DECLARE @dbName VARCHAR(128) = DB_NAME()
     DECLARE @schemaName VARCHAR(128) = OBJECT_SCHEMA_NAME(@objId)
     DECLARE @spName VARCHAR(128) = OBJECT_NAME(@objId)
    
     DECLARE @evtId INT 
    
     EXEC @evtId = _Admin.dbo.usp_MakeLogEntry
       @srcDb = @dbName
     , @srcSpSchema = @schemaName
     , @srcSpName = @spName
     , @msg = @msg
    
     RETURN @evtId
    
   END

   Note that:

   • You need a copy of usp_LogEvt in any database from which you want to write log entries. This is so that incoming object IDs (@objId) can be resolved correctly to database and object names.¹⁾
   • The EXEC call inside usp_LogEvt is fully qualified, to make sure that you can call it from any database.
   • If your administration database isn't called _Admin, you'll need to change the EXEC call appropriately.
   • You might want to think about creating it in model so that it is automatically added to every new database.

The log entry ID is returned from the call to usp_LogEvt. You don't need to use it at all, but if it is of interest you can do something like:

DECLARE @evtId INT
EXEC @evtId = dbo.usp_LogEvt 'Here is a message', @@PROCID
PRINT @evtId  -- this is the logged message's EventId