KofiFeb 10, 2020 | edited Feb 10, 2020 - by @sqldibia

I have a question regarding FORMATMESSAGE; the 3 examples below demonstrates my understanding of the use of FORMATMESSAGE;

example 1

DECLARE @d INT = 123;
SELECT FORMATMESSAGE('Hey %s, %d', @s, @d)

example 2

SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS Result;

example 3

DECLARE @ExampleMessage VARCHAR(150)
SET @ExampleMessage = 
FORMATMESSAGE ('SQL Server %s, has some amazing new features and learn them on %s!', '2016','MyTechMantra.com');
SELECT @ExampleMessage;

How do they work using variables...the usage seems kinda advance or different in the below examples. What i'm i missing?

when i execute it, only the first parameter "@Subject" returns, the second doesn't

DECLARE @Subject VARCHAR(250)='English',@MessageText VARCHAR(500),@Name VARCHAR(50)= '[ERIC BRONX]'
SELECT @MessageText = FORMATMESSAGE(@Subject,@Name);
select @MessageText

Also, i'm i doing this execution below wrongly? its returning error...... How does the positioning of "%S., %S" play out here?

SELECT FORMATMESSAGE('We travelled around the world in 80 days %S., %S', 'name', 'Location')
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.

Kofi Author Feb 11, 2020

Awesome! Thanks for sharing!

edited Feb 12, 2020 - by @sqldibia
Kofi+ Follow
joinedDec 28, 2019

More from @sqljunkyking