I've worked in an SQL Server environment before where logging was made part of our stored procedures to capture execution start/end, parameter values and error messages which I found very useful and is something I'm looking to introduce in a new environment.
The tables used for this logging looked like something below, parameters were captured using
INSERT statements into a table with the with values being implicitly cast to
CREATE TABLE dbo.Execution ( Id INT IDENTITY(1,1) NOT NULL , SchemaName NVARCHAR(128) NOT NULL , ProcedureName NVARCHAR(128) NOT NULL , ExecutionStart DATETIME NOT NULL , ExecutionEnd DATETIME NULL , ExecutionFailed BIT NOT NULL ) CREATE TABLE dbo.ExecutionError ( Id INT IDENTITY(1,1) NOT NULL , ExecutionId INT NOT NULL , CustomErrorMessage NVARCHAR(8000) NULL , SqlErrorMessage NVARCHAR(8000) NULL ) CREATE TABLE dbo.ExecutionParameter ( Id INT IDENTITY(1,1) NOT NULL , ExecutionId INT NOT NULL , ParameterName NVARCHAR(128) NOT NULL , ParameterValue NVARCHAR(MAX) NULL )
I've been reconsidering the
ExecutionParameter table with the possibility of using an
SQL_VARIANT data type so I could get the base data type if ever needed for analysis and reporting purposes without having to guess based on the name/value.
However this wouldn't work for parameters that have a data type of
VARCHAR(MAX) and would therefore need to still have the
NVARCHAR(MAX) column there which would be
NULL the majority of the time.
The use of
SQL_VARIANT is tempting but I feel the original table structure works fine and can't really be made any better without making the process more complicated.
Is this something you have done previously, if so how did you implement it? Can you see room for improvement in the above schema without making it overly complicated?
Perhaps an additional table with details of the stored procedure and their parameters which can be referenced? Though I feel this would be difficult to maintain and become confusing as stored procedures are modified over time.