A few weeks ago, I wrote a post on using temporary stored procedures in SQL Server. Kevin Feasel of Curated SQL had this reply Using Temporary Stored Procedures to Output Common Messages. I had heard of FORMATMESSAGE before, but I had completely coupled this in my mind with formatting SQL Server error messages. (Which turns out to be a great use of this tech)

Even the Copilot description was:
“The FORMATMESSAGE function has been available in SQL Server since SQL Server 2008. It allows you to format messages using a message stored in the sys.messages catalog or a custom message string, substituting parameter values into placeholders.”

It was once sort of common to create your own error messages, and then to use this function to format it. It was never something I did, so this absolutely never dawned on me. And when I tried to play with it with a random error message I found, I was not able to make it work. Honestly, if I thought there was any value for you or me in figuring it out, I would.

Basics of FORMATMESSAGE

The way that Kevin suggested was pretty much the way I am going to demonstrate (and the alter my procedure to work using that method) is the use string placeholders. You can set up a string like:

DECLARE @String nvarchar(100) = N'String1: %s String2: %s Integer: %d';

And then user FORMATMESSAGE and pass in two string variables and an integer two match the two %s tokens and then the %d token.

There are other types of tokens which I could only find easily documented by asking Copilot, but all I really need for my final solution is %s.

DECLARE @String nvarchar(100) = N'String1: %s String2: %s Integer: %d';
SELECT FORMATMESSAGE(@String,N'First',N'Second',1);

The output is:

-----------------------------------------
String1: First String2: Second Integer: 1

If you supply too many parameters:

DECLARE @String nvarchar(100) = N'String1: %s String2: %s Integer: %d';
SELECT FORMATMESSAGE(@String,N'First',N'Second',1,'Hello','Goodbye');

The extras are ignored and I get the same output. If you pass in the wrong data type:

DECLARE @String nvarchar(100) = N'String1: %s String2: %s Integer: %d';
SELECT FORMATMESSAGE(@String,1,2,3);

This returns an error, and not a really detailed error:

Error: 50000, Severity: -1, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

If you don’t pass in enough parameters:

DECLARE @String nvarchar(100) = N'String1: %s String2: %s Integer: %d';
SELECT FORMATMESSAGE(@String,N'First');

The tokens are replaced by (null):

-----------------------------------------------
String1: First String2: (null) Integer: (null)

Kevin’s suggestion to change my code.

I had used a kind of messy long CONCAT expression in a SET statement:

SET @message = CONCAT('',@Message + ' : ',
       'Message Time - ' + CASE WHEN @AddTimeToMessageFlag = 1
                                THEN CAST(SYSDATETIME() AS varchar(100))
                                ELSE NULL END + ' : ',
       'ProcessId - ' + CASE WHEN @AddSpidToMessageFlag = 1
                             THEN CAST(@@spid AS varchar(100))
                             ELSE NULL END + ' : ',
       'LoggedInUserId - ' + CASE WHEN @AddOriginalLoginToOutputFlag = 1
                             THEN original_login() ELSE NULL END + ' : ');

I am not going to say my version wasn’t okay, but looking at Kevin’s suggestion, this was clearly cleaner:

SET @message = FORMATMESSAGE(N'%s%s%s%s', @Message,
      CASE
            WHEN @AddTimeToMessageFlag = 1
            THEN CONCAT(N' : Message Time - ', SYSDATETIME())
            ELSE N''
      END,
      CASE
            WHEN @AddSpidToMessageFlag = 1
            THEN CONCAT(N' : ProcessId - ', @@spid)
            ELSE N''
      END,
      CASE
            WHEN @AddOriginalLoginToOutputFlag = 1 THEN
           CONCAT(N' : LoggedInUserId - ', original_login())
      ELSE N''
END);

So, I decided to change my procedure.

Changing my Code

In this section is the new version of my temp stored procedure to output a status message. You can see that my code no longer makes one large string, but instead using a FORMATMESSAGE call that has four string tokens. Each of the bits I output are now strings that either have the message requested, or if it wasn’t requested, it returns an empty string.

The changes are made in the section between the —– comment marks.

CREATE OR ALTER PROCEDURE #OutputMessageNOW 
@Message nvarchar(1000) = 'Default Message', --set null if not needed
@AddTimeToMessageFlag bit = 1, --adds system time to the message
@AddSpidToMessageFlag bit = 0, --add spid to the message
@AddOriginalLoginToOutputFlag bit = 0 --add the user who is logged in
AS 
BEGIN 
-----------------------------------------------
DECLARE @output nvarchar(1000)
SET @output = FORMATMESSAGE(N'%s%s%s%s',
       --message
       COALESCE(@Message,''),

       --messagetime
       CASE WHEN @AddTimeToMessageFlag = 1 
            THEN CONCAT('Message Time - ',
                     CAST(SYSDATETIME() AS varchar(100)),' : ')
            ELSE '' END,

       --process id
       CASE WHEN @AddSpidToMessageFlag = 1 
            THEN CONCAT('ProcessId - ',@@SPID,' : ')
            ELSE '' END,

       --logged in user
       CASE WHEN @AddOriginalLoginToOutputFlag = 1 
            THEN CONCAT('LoggedInUserId - ',ORIGINAL_LOGIN(),' : ')
            ELSE '' END)
------------------------------------------------

SET @output = SUBSTRING(@output,1,LEN(@output) - 2);

RAISERROR(@output,10,1) WITH NOWAIT; 

END;

I will definitely agree that the new section for formatting the output is a whole lot easier to follow now with each bit of the output independently computed.

Why not just use RAISERROR?

As I was making these changes, I realized that something I had read mentioned RAISERROR and using the same formatting. Taking my test strings and FORMATMESSAGE calls, I decided to try it. As a reminder:

SELECT FORMATMESSAGE(N'String1: %s String2: %s','First','Second');

This returns:

=-----------------------------
String1: First String2: Second

You can also use RAISERROR in much the same way, with the two extra parameters for severity and state followed by the token replacement parameters. For example, execute:

RAISERROR(N'String1: %s String2: %s',16,1,'First','Second');

This returns the same message asked for, and it does it directly as part of the statement we are trying to execute. I made it an error message just to make it obvious that it was different.:

Msg 50000, Level 16, State 1, Line 133
String1: First String2: Second

Change that to a level 10 or less and you get the same output style as I use in my temporary procedure.

However…as I tried to make this change, to convert the FORMATMESSAGE command directly into a RAISERROR, there was a problem. While using literals as I did earlier worked great, complex parameters were a no go.

RAISERROR(N'String1: %s String2: %s',10,1,COALESCE('First',''),'Second');

This gave me an error:

Msg 156, Level 15, State 1, Line 148
Incorrect syntax near the keyword 'COALESCE'.

And so did a simple concatenation, with an error message that said '+' instead of 'COALESCE':

RAISERROR(N'String1: %s String2: %s',10,1,'First' + '3','Second')

Variable do work however:

DECLARE @Parm1 nvarchar(10) = 'Parameter';
RAISERROR(N'String1: %s String2: %s',10,1,@parm1,@parm1);

This outputs:

String1: Parameter String2: Parameter

But end the end, while I could conceivably change my procedure to use a variable per section to output and use RAISERROR directly, for the foreseeable future, this version based on FORMATMESSAGE seems like a better method. It is something interesting to know about RAISERROR and FORMATMESSAGE, so it isn’t time wasted (for me).

Summary

Thank you Kevin Feasel for teaching me yet another thing. Your input made my procedure better, taught me a new trick, and hopefully through your site and mine, even more tricks they might use again someday.

Again, a lot about what blogging is about is learning and sharing new techniques.