SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Undocumented functionality in FORMATMESSAGE

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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...