Understanding FORMATMESSAGE() in SQL Server

by

When you’re working with SQL Server, sometimes you don’t just want to throw an error. Sometimes you want to build a message you can actually use elsewhere. That’s where FORMATMESSAGE() comes in. Instead of immediately printing a message like RAISERROR does, FORMATMESSAGE() gives you the formatted string back so you can decide what to do with it. This could include logging it, storing it, displaying it, or simply passing it along.

In simple terms, you can think of it as a way to take a predefined message from sys.messages (or even a custom string you provide) and turn it into a neatly formatted output. This can be quite handy when you need more control over how messages are handled in your SQL workflows.

Syntax

The official syntax goes like this:

FORMATMESSAGE ( { msg_number  | ' msg_string ' | @msg_variable} , [ param_value [ ,...n ] ] )

Here’s a quick explanation of the arguments:

  • msg_number: The ID of a message stored in sys.messages.
  • 'msg_string': A literal string provided directly in the function. The structure of a message string and the use of parameters in the string is the same as with RAISERROR. See Microsoft’s documentation on RAISERROR for reference.
  • @msg_variable: A variable containing the message text.
  • param_value [ ,...n ]: One or more values that replace the placeholders in the message.

You must provide at least one of msg_number, 'msg_string', or @msg_variable to define the message text.

The param_value [ ,...n ] part is optional. This is only needed if the message has placeholders (%s, %d, etc.) that need values. If there are no placeholders, you can leave this part out.

Example 1 – Basic Usage

We’ll start with a very simple example in order to demonstrate the concept:

SELECT FORMATMESSAGE('Hello %s', 'World');

Output:

Hello World

You can’t beat “Hello World” when it comes to simplistic examples. In this case we used %s as a placeholder for World.

While I referred to the %s part as a placeholder, the Microsoft documentation refers to it as a “conversion specification”. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_string. Conversion specifications have this format:

% [[flag] [width] [. precision] [{h | l}]] type

In our case, we only specified the type. This is referred to as a “type specification”.

Example 2 – Two Arguments

Let’s step it up a notch with two arguments:

SELECT FORMATMESSAGE('Hello %s, you have %d new messages', 'Stacy', 3);

Output:

Hello Stacy, you have 3 new messages

What’s happening here:

  • The string has two placeholders:
    • %s expects a string (in this case it’s 'Stacy')
    • %d expects a signed integer (in this case it’s 3). We can also use %i.
  • FORMATMESSAGE() replaces them in order.

Example 3 – Using Messages from sys.messages

The Microsoft documentation states that the FORMATMESSAGE() function “constructs a message from an existing message in sys.messages or from a provided string.”. Therefore, the main purpose of this function is to work with messages that are already in sys.messages. So let’s do that now.

We can retrieve messages in sys.messages with a simple SELECT statement. Here’s an example of an existing message:

SELECT text FROM sys.messages WHERE message_id = 21171 AND language_id = 1033;

Output:

Could not find package '%s' in msdb at server '%s'.

We can see that the message contains conversion specifications, both of which are %s. We can customize this message by passing our own variables that will be used to replace these conversion specifications.

For example:

SELECT FORMATMESSAGE(21171, 'Package Name', 'Server Name');

Output:

Could not find package 'Package Name' in msdb at server 'Server Name'.

Example 4 – Creating a Custom sys.messages Message

We can also create our own messages to be stored in sys.messages. We can do this with the sp_addmessage stored procedure:

EXEC sp_addmessage   
    @msgnum = 50001,  
    @severity = 16,  
    @msgtext = N'User %s has logged in at %s';

Now we can use FORMATMESSAGE() with the message number instead of writing the string directly:

SELECT FORMATMESSAGE(50001, 'Blake', '10:30 AM');

Output:

User Blake has logged in at 10:30 AM

Example 5 – Signed Integers

We can use a plus sign (+) to display signed integers with their sign:

SELECT FORMATMESSAGE('Unsigned: %i, Signed: %+i', 5, 5);

Output:

Unsigned: 5, Signed: +5

If our variable is prefixed with a plus sign then it’s removed in the unsigned integer. However, the same can’t be said for the minus sign:

SELECT FORMATMESSAGE('(Not) Unsigned: %i, Signed: %+i', -5, -5);

Output:

(Not) Unsigned: -5, Signed: -5

Unsigned integers can only be positive. They can’t be negative. So by including the minus sign, we’re explicitly making it a signed integer.

Example 6 – Unsigned Integers

We can use %u to display the value as an unsigned integer:

SELECT FORMATMESSAGE('%u, %+u', +50, +50);

Output:

50, 50

So in this case it didn’t matter how we tried to force a signed integer, the %u displayed it as an unsigned integer.

However, with negative values, it can be a bit different:

SELECT FORMATMESSAGE('%u, %+u', -50, -50);

Output:

4294967246, 4294967246

At first, it might look like SQL Server mucked up the result. But there’s a method in the madness.

When you pass -50 to %u, it doesn’t magically become “invalid.” Instead, the bit pattern of -50 is reinterpreted as an unsigned 32-bit value.

Bitwise reinterpretation

In two’s complement, -50 in 32 bits is:

11111111 11111111 11111111 11001110
  • Interpreted as a signed integer, this is -50.
  • Interpreted as an unsigned integer, it’s 4294967246.

So SQL Server is showing us the raw 32-bit unsigned interpretation of the same bits.

Example 7 – Hexadecimal Values

We can use %x or %X to display the value as an unsigned hexadecimal value:

SELECT FORMATMESSAGE('%x, %x, %x, %x', 7, 18, 95, 2789);

Output:

7, 12, 5f, ae5

And here’s an example with negative values:

SELECT FORMATMESSAGE('%x, %x, %x, %x', -7, -18, -95, -2789);

Output:

fffffff9, ffffffee, ffffffa1, fffff51b

Using an uppercase X returns the value in uppercase:

SELECT FORMATMESSAGE('%X, %X, %X, %X', -7, -18, -95, -2789);

Output:

FFFFFFF9, FFFFFFEE, FFFFFFA1, FFFFF51B

Example 8 – Unsigned Octals

We can use %o to display the value as an unsigned octal:

SELECT FORMATMESSAGE('%o, %o', 50, -50);

Output:

62, 37777777716

Localization

Microsoft’s documentation states:

FORMATMESSAGE looks up the message in the current language of the user. For system messages (msg_number <=50000), if there is no localized version of the message, the OS language version is used. For user messages (msg_number >50000), if there is no localized version of the message, the English version is used.

For localized messages, the supplied parameter values must correspond to the parameter placeholders in the U.S. English version. That is, parameter 1 in the localized version must correspond to parameter 1 in the U.S. English version, parameter 2 must correspond to parameter 2, and so on.

So it pays to bear this in mind when constructing messages.