Undocumented functionality in FORMATMESSAGE2
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) + '%s' 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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, language sql, microsoft sql server, sql statements, T-SQL
2 thoughts on “Undocumented functionality in FORMATMESSAGE”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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.
At a guess you are using a version prior to 2012. I tried it on my 2008 instance and got the same message but when I run it on a 2012+ it works fine.