t-sql logging

This is post one of a long series of SQL Server posts (t-sql) that will culminate in a CRUD'able schema and an automated Nordic database structure.  Eventually all of the code will be available as an open source project on github.

The Nordic database structure leads to some very interesting capabilities;  One of my favorites is universal directed graph relationships between any tables/rows, with the options to config what type of nodes an edge is allowed to connect.  This is some very powerful stuff.

The first couple of articles are going to build up the helper utilities and functions that I tend to use everywhere. I tend to write my t-sql code in such a way that any given script file can be ran at any time.  The scripts drop all the objects that will be created first and then recreates them. 

The first function is a generalized logging function with configurable output level (error, warning, info).  All logging is also written to a log table (dbo.metaLog).  The output level is persisted using SET context_info. 

We'll end up with two functions: dbo.SetLogOutputLevel and dbo.Log

IF Object_id('dbo.SetLogOutputLevel') IS NOT NULL
  DROP PROCEDURE dbo.setlogoutputlevel;

go

--set and persist debug output level use by sp dbo.log
CREATE PROCEDURE dbo.Setlogoutputlevel(@level CHAR(1))
AS
  BEGIN
      DECLARE @BinVar VARBINARY(128);

      SET @BinVar = Cast(@level AS VARBINARY(128));
      SET context_info @BinVar;
  END

go

EXEC dbo.Setlogoutputlevel
  'W';

go

--if not present create log table used by dbo.log
IF Object_id('dbo.metaLog') IS NULL
  CREATE TABLE dbo.metalog
    (
       id       BIGINT IDENTITY(1, 1) PRIMARY KEY,
       dt       DATETIME DEFAULT(Getdate()),
       who      SYSNAME DEFAULT SYSTEM_USER,
       [level]  CHAR(1) DEFAULT('I'),
       detail   NVARCHAR(max),
       [schema] SYSNAME,
       [name]   SYSNAME,
       [sql]    NVARCHAR(max) NULL
    )

go

IF Object_id('dbo.log') IS NOT NULL
  DROP PROCEDURE dbo.log;

go

--universal log function
--print based on log level found in CONTEXT_INFO()
--insert all logs to dbo.metLog table
--use like:  exec dbo.log 'dbo', 'DropObject', 'W', 'Did not find object "', @object, '" of type "', @type, '"';
CREATE PROCEDURE dbo.Log (@schema SYSNAME='',
                          @name   SYSNAME='',
                          @level  CHAR(1)='',
                          @detail NVARCHAR(max)='',
                          @d1     NVARCHAR(max)='',
                          @d2     NVARCHAR(max)='',
                          @d3     NVARCHAR(max)='',
                          @d4     NVARCHAR(max)='',
                          @d5     NVARCHAR(max)='')
AS
  BEGIN
      SET nocount ON;

      DECLARE @l CHAR(1);

      SELECT @l = Cast(Context_info() AS CHAR(1));

      -- if context_info value is non sensical use most verbose 'I'
      IF @l NOT IN ( 'E', 'W', 'I', 'V', 'S' )
        BEGIN
            --exec dbo.SetLogOutputLevel 'V';
            SELECT @l = 'I';
        END

      DECLARE @mess NVARCHAR(max);

      SET @mess = @detail + Isnull(@d1, '') + Isnull(@d2, '')
                  + Isnull(@d3, '') + Isnull(@d4, '')
                  + Isnull(@d5, '');

      DECLARE @sql NVARCHAR(max);

      IF Object_id('dbo.metaLog') IS NOT NULL
        INSERT INTO dbo.metalog
                    ([schema],
                     name,
                     [level],
                     detail)
        VALUES      (@schema,
                     @name,
                     @level,
                     @mess);
      ELSE
        PRINT '***table dbo.metaLog not present';

      IF @l = 'S'
        RETURN;

      IF @level LIKE 'E'
        PRINT '*** ERROR:  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;

      IF @level LIKE 'W'
         AND @l IN ( 'W', 'I', 'V' )
        PRINT ' ** WARN :  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;

      IF @level LIKE 'I'
         AND @l IN ( 'I', 'V' )
        PRINT '  * INFO :  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;

      IF @level LIKE 'V'
         AND @l = 'V'
        PRINT '    All  :  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;
  END

go