Undocumented functionality in FORMATMESSAGE


December 9, 2013 by Kenneth Fisher

I was reading a rather interesting post on stackexchange “Why is % a forbidden char in a THROW message?” (which is an interesting read in and of itself) and noticed something interesting in the OP’s example.

FORMATMESSAGE('Procedure input %s is wrong', @ProcParam)

That’s pretty cool. I remember using %s and %n in other languages (bat files in particular) and always found it very handy. The same functionality is available in RAISERROR. In the BOL Entry for RAISERROR it says the initial string parameter “Is a user-defined message with formatting similar to the printf function in the C standard library.” So I went into BOL(obviously) and what did I come up with? Based on BOL passing a string as the first parameter of FORMATMESSAGE isn’t possible. And in fact in 2008 it isn’t. It is defined as msg_number which I believe is a subset of int. This number defines the message from sys.messages entry that you want to use.

BOL says FORMATMESSAGE is specifically for “Constructing a message from an existing message in sys.messages.” and that is exactly what it does in 2008 and 2008 R2. In 2012 however Microsoft added additional functionality, but unfortunately it didn’t show up in BOL.

Not a big deal really, but the new functionality itself is really cool! It takes it from something mostly only useful with error messages to something that can process any message you want to construct.

Let’s say for example you want to do a letter.

DECLARE @Letter varchar(8000) = 
		'Dear %s' + char(13) + char(13) +
		'You owe us %i.  Please pay promptly.' + char(13) + char(13) +
		'Yours Sincerly' + char(13) + 
DECLARE @Message VARCHAR(max) = 
		FORMATMESSAGE(@Letter, 'John Smith',10000,'Jane Doe');
PRINT @Message;

I should note that the first parameter will only handle a varchar(8000) or nvarchar(4000). No (max) columns which does limit things somewhat. Now there are other (better) ways of handling this, but it’s always nice to have different ways to solve a problem. And to be honest under certain circumstances this may be the way I go.

2 thoughts on “Undocumented functionality in FORMATMESSAGE

  1. jimbobmcgee says:

    You sure about this?

    Any time I pass anything other than an integer message id to FORMATMESSAGE, I get the following error:

    Msg 8116, Level 16, State 1, Line 3:
    Argument data type varchar is invalid for argument 1 of formatmessage function.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: