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.

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