Re:How does TSQL FORMATMESSAGE Work? View Post

[Full Discussion]
Emeka Okeke Author Feb 10, 2020

The purpose of TSQL FORMATMESSAGE function is to print or output formated string to the caller.

The below excerpt from your first query simply tells the query engine to take string placeholder params %s and %d and replace them with the values of parameters @s and @d respectively.

SELECT FORMATMESSAGE('Hey %s, %d', @s, @d)

Notice that I said respectively? This is very important because it shows how FORMATMESSAGE function really works.

So the first argument of this function is the string to be formatted, and the subsequent arguments are the strings to be used to replace the placeholder params used in the string to be formatted in the order in which they appear.

So in this case, the first placeholder that appeared in the string to be formatted parameter - which is the first parameter of that function - %s is replaced with the first string parameter @s and the second placeholder %d is replaced with the second parameter @d.

By now you should know how this function works and how to use it. However, there is nothing wrong with your fourth query with some part of it below:

SELECT @MessageText = FORMATMESSAGE(@Subject,@Name); 

The parameter @Subject does not have any string placeholder param to be replaced, so the second parameter @Name is ignored and the function simply returns the @Subject string value as it is.

Finally, your last query below:

SELECT FORMATMESSAGE('We travelled around the world in 80 days %S., %S', 'name', 'Location')

used upper case letter %S in declaring the placeholder param which is not allowed, hence the error you got when you executed that query.