sp_EchoMessage
Not documented at this time.
@@@---@@@
This SQL script defines and creates the stored procedure
[Util].[sp_EchoMessage]. This utility procedure is designed to print
messages to the console (or output window) during deployment or
execution, with the option to control verbosity via a configuration
setting. It also logs these messages to the [dbo].[Log] table.
Script Breakdown
1. Metadata, Versioning, and Idempotent
Placeholder Creation
The script follows the standard deployment
pattern for procedures:
Metadata Declaration:
SQL
DECLARE @Name [varchar](128), @Description [varchar](128),
@Version [int], @Type [varchar](5);
SET @Version=240822;
SET
@Type='P';
SET @Name= 'sp_EchoMessage';
SET @Description=
'Print a message to the screen';
These lines define the metadata
for sp_EchoMessage, including its version (240822).
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.
EXEC
[Deploy].[sp_CreateEmptyObject] @Type=@Type, @Name=@Name,
@Schema='Util';: This command ensures a placeholder for
sp_EchoMessage exists in the [Util] schema, guaranteeing the ALTER
PROCEDURE statement will succeed.
GO: Separates the setup
commands from the procedure definition.
2.
[Util].[sp_EchoMessage] Stored Procedure Definition
This is the
main logic for echoing messages:
Parameters:
@Parm1
[nvarchar] (255) = NULL: An optional parameter that, if provided,
maps to predefined message prefixes (e.g., 'D' for 'Deploying').
@Message [nvarchar] (255) = NULL: An optional additional message
text to append to the predefined prefix or print alone.
@EchoOn [bit]=0: An optional parameter to explicitly force echoing
to be ON (1) or OFF (0). Defaults to 0.
Internal Variables:
@FullMessage [nvarchar] (255): Stores the complete message compiled
from @Parm1 and @Message.
@Exists [bit]: Used to store the
result of the sp_GetIsExistObject call.
Logic:
SET NOCOUNT
ON;: Suppresses row count messages.
Determine EchoOn State
(Priority Order):
Override by @EchoOn parameter: The
procedure first checks if the @EchoOn parameter was explicitly set
to 1. If it's 1, echoing is forced on. If it's 0 (the default), it
proceeds to check other sources for the echo setting.
Check
#Vars temporary table:
SQL
IF
OBJECT_ID('tempdb..#Vars') IS NOT NULL AND @EchoOn != 1
SELECT
@EchoOn = [Value] FROM #Vars WHERE [Key]='EchoOn';
If the
temporary #Vars table exists and @EchoOn was not explicitly 1, it
attempts to retrieve the EchoOn setting from #Vars. This is useful
for controlling echoing within a single deployment script run.
Check [Lookup].[Config] permanent table:
SQL
IF
@EchoOn = 0
BEGIN
EXEC [Util].[sp_GetIsExistObject] @Type='T',
@Schema='Lookup', @Name='Config', @ReturnValue=@Exists OUTPUT
IF
@Exists = 1
SELECT @EchoOn = [Value] FROM [Lookup].[Config] WHERE
[Key]=3 /* EchoOn */
END
If @EchoOn is still 0 (meaning it
wasn't forced ON by the parameter or found ON in #Vars), it then
checks the persistent [Lookup].[Config] table. It first verifies the
Config table exists using [Util].[sp_GetIsExistObject] (a good
defensive check) and then attempts to retrieve the EchoOn setting
from ConfigKey = 3. This provides a permanent, default control over
echoing.
Construct @FullMessage:
Predefined Prefixes
for @Parm1: If @Parm1 is not NULL, it is checked against a series of
single-character codes ('A', 'D', 'E', 'P', 'S', 'U', 'V') and
replaced with a descriptive, padded string (e.g., 'Applied :',
'Deploying :').
SET @FullMessage = @Parm1 + ' ': The
(possibly translated) @Parm1 is assigned to @FullMessage with a
space.
Commented-out lines: --SET @FullMessage = @FullMessage
+ @CallingEntityVers + ' - '+ @CallingEntityName + '' suggests a
future or previous intention to dynamically include calling entity
version/name, which is not currently implemented.
Append
@Message: If @Message is not NULL, it is concatenated to
@FullMessage.
Print Message (Conditional):
IF @EchoOn
= 1 PRINT @FullMessage;: The compiled message is printed to the SQL
Server Messages window (or client output) only if @EchoOn is 1.
Log Message:
EXEC [Util].[sp_InsertLog] @FullMessage;:
Regardless of whether the message was echoed to the console, it is
always inserted into the [dbo].[Log] table using
[Util].[sp_InsertLog]. This ensures a persistent record of all
messages generated by sp_EchoMessage, even if console echoing is
off.
Purpose and Usage
[Util].[sp_EchoMessage] is a
versatile logging and communication utility within the dbOmnibus
framework.
Runtime Feedback: Provides immediate feedback
during deployment scripts or long-running operations by printing
messages to the console.
Centralized Logging: Ensures all
messages (whether printed or not) are permanently stored in the
[dbo].[Log] table for auditing, debugging, and historical analysis.
Configurable Verbosity: Allows granular control over how much
information is displayed during execution via temporary variables
(#Vars) or persistent configuration ([Lookup].[Config]). This is
very useful for different environments (e.g., verbose in dev,
minimal in prod).
Standardized Messaging: The predefined
@Parm1 prefixes provide a consistent format for common deployment
messages (e.g., "Deploying : ObjectName").
Example Usage
(from script comments and common scenarios):
SQL
--
During a deployment script, to indicate progress:
EXEC
[Util].[sp_EchoMessage] @Parm1='D', @Message='Starting database
schema deployment...';
-- To indicate an object being
applied:
EXEC [Util].[sp_EchoMessage] @Parm1='A', @Message='Table
[dbo].[Customers]';
-- To print a custom message:
EXEC
[Util].[sp_EchoMessage] @Message='Finished all setup steps.';
-- To force a message to echo, even if global EchoOn is off:
EXEC [Util].[sp_EchoMessage] @Message='Critical alert: Disk space
low!', @EchoOn=1;
This procedure significantly enhances the
traceability and user experience of automated database deployments.
Page Last Updated: 11 July 2025