====== Simple message logging ======
This article describes an approach to one-line, simple event logging from within stored procedures; something like this:
<code tsql>EXEC dbo.usp_LogEvt 'Here is a message', @@PROCID</code>

===== Setting it up =====
  -- 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.
  .. <code tsql>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
)
</code>
  -- Create an SP in the same database to write entries into the log
  .. <code tsql>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</code>
  -- ''usp_MakeLogEntry'' is not intended to be called directly -- create this wrapper SP:
  .. <code tsql>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
</code>
  .. 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.((For the same reason, don't ever write ''EXEC //DbName//.dbo.usp_LogEvt'' -- you want to ensure that you call the local version or your log entries will become meaningless!))
    * 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.

===== Returning event IDs =====
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:
<code tsql>DECLARE @evtId INT
EXEC @evtId = dbo.usp_LogEvt 'Here is a message', @@PROCID
PRINT @evtId  -- this is the logged message's EventId
</code>

{{tag>>logs admin}}