sp_InsertLog
Add entries into the [Log] table.
Usage:
EXEC [Util].[sp_InsertLog] @Entry='my Log Entry'
@@@---@@@
This SQL script defines and creates the stored procedure
[Util].[sp_InsertLog]. This utility procedure is designed to insert
log entries into the [dbo].[Log] table, with a built-in mechanism to
control logging verbosity based on a configurable logging level.
Script Breakdown
1. Metadata, Versioning, and Idempotent
Placeholder Creation
The script follows the standard deployment
pattern for procedures within this framework:
Metadata
Declaration:
SQL
DECLARE @Name
[varchar](128),@Description [varchar](128),@Version [int],@Type
[varchar](5);
SET @Version=241006;
SET @Type='P';
SET
@Name= 'sp_InsertLog';
SET @Description= 'Add entries into the
[Log] table';
These lines define the metadata for the
sp_InsertLog procedure, including its version (241006).
EXEC
[Deploy].[sp_UpdateEntityVer]
@Type=@Type,@Name=@Name,@Description=@Description,@Version=@Version;:
This call records the procedure's version in the temporary
#EntityVers table for the current deployment session. This record
will eventually be persisted to [dbo].[_EntityVersions].
EXEC
[Deploy].[sp_CreateEmptyObject]
@Type=@Type,@Name=@Name,@Schema='Util';: This command ensures that a
placeholder for sp_InsertLog exists in the [Util] schema. This is a
robust way to guarantee that the subsequent ALTER PROCEDURE
statement will execute successfully, whether the procedure is being
created for the first time or updated.
GO: This acts as a
batch separator, ensuring the placeholder creation completes before
the ALTER PROCEDURE command is processed.
2.
[Util].[sp_InsertLog] Stored Procedure Definition
This is the
main logic for inserting log entries:
Parameters:
@Entry
[nvarchar](255): Mandatory. The actual text of the log entry.
nvarchar(255) matches the [Entry] column in the [dbo].[Log] table,
supporting Unicode characters.
@Level [int]=9: Optional. The
logging level of the current log entry. Defaults to 9. A higher
number typically means a more verbose or detailed log.
Internal Variables:
@LoggingLevel [int]: Stores the configured
global logging level retrieved from [Lookup].[Config].
@LoggingLevelVar [varchar](20): A temporary variable to hold the
string value retrieved from [Lookup].[Config] before casting it to
int.
@Now [DateTime]: Stores the current timestamp for the
log entry.
Logic:
SET NOCOUNT ON;: Suppresses messages
indicating the number of rows affected by SQL statements. This is a
standard best practice in stored procedures.
Retrieve Logging
Level from Configuration:
EXEC [Util].[sp_GetConfigValue]
@ConfigKey=7, @ReturnValue=@LoggingLevelVar OUTPUT;: This calls the
[Util].[sp_GetConfigValue] procedure to retrieve the global logging
level. It expects the logging level to be stored in the
[Lookup].[Config] table under ConfigKey = 7.
SET
@LoggingLevel = CAST(COALESCE(@LoggingLevelVar,5) as [int]);: This
line converts the retrieved logging level (which comes as varchar
from sp_GetConfigValue) to an int. COALESCE(@LoggingLevelVar,5)
ensures that if ConfigKey = 7 is not found or returns NULL, a
default LoggingLevel of 5 is used.
Logging Level Check:
IF @Level > @LoggingLevel RETURN;: This is the core filtering
logic. If the @Level of the current log entry is greater than the
globally configured @LoggingLevel, the procedure immediately exits
(RETURN) without inserting the log entry. This means only entries
with a level less than or equal to the configured level will be
logged. For example, if @LoggingLevel is 5:
An entry with
@Level = 1 (e.g., Critical) will be logged.
An entry with
@Level = 5 (e.g., Info) will be logged.
An entry with @Level
= 9 (e.g., Debug) will not be logged.
Insert Log Entry:
DECLARE @Now [DateTime]; SET @Now = GETDATE();: Gets the current
timestamp.
INSERT INTO [Log] ([Time], [Entry]) VALUES (@Now,
@Entry);: Inserts the new log entry with the current timestamp and
the provided log message into the [dbo].[Log] table.
Purpose
and Usage
[Util].[sp_InsertLog] is the primary mechanism for
application logging within the database.
Centralized Logging:
Provides a consistent and controlled way to write log entries to the
[dbo].[Log] table.
Configurable Verbosity: The integration
with [Lookup].[Config] allows administrators to dynamically adjust
the desired logging level without code changes, making the
application's logging behavior more flexible.
Development and
Operations Support: Essential for debugging, monitoring, and
auditing application behavior in various environments (development,
test, production).
Example Usage (from script comments):
SQL
-- Assuming ConfigKey 7 in [Lookup].[Config] is set
to, for example, '5'.
-- This entry will be logged (level 1
<= global level 5)
EXEC [Util].[sp_InsertLog] @Entry='Application
started successfully.', @Level=1;
-- This entry will be
logged (level 5 <= global level 5)
EXEC [Util].[sp_InsertLog]
@Entry='Processing batch file: daily_report.csv', @Level=5;
-- This entry will NOT be logged (level 9 > global level 5)
EXEC
[Util].[sp_InsertLog] @Entry='Detailed debug info for function X.',
@Level=9;
This procedure is a robust and well-designed component
for managing logging within a SQL Server application.
Page Last Updated: 21 September 2025