String Interpolation with FORMATMESSAGE function in SQL Server 2016

  FormatMessage has been with SQLServer since the 2008 version. It used to construct a message from an existing defined message in sys.messages table. With SQL Server 2016, we can use this function to construct a message from any string.

 FormatMessage function works just like .NET's String.Format function. In .NET you can construct a message like the following example :

String.Format("The current price is {0} per ounce.", "$5");

RESULT

The current price is $5 per ounce.

Now, we can do the same thing in SQL World by using FORMATMESSAGE function with 2016 version.

FORMATMESSAGE ( { msg_number  | ' msg_string ' } , [ param_value [ ,...n ] ] )

Here is some examples:

SELECT FORMATMESSAGE('Hello %s!', 'World')

Hello World!

SELECT FORMATMESSAGE('This row is updated by %s on %s', 'Bob', CONVERT(varchar, GETDATE(),101))

This row is update by Bob on 04/07/2017

SELECT FORMATMESSAGE('Zipcode is %i', 12000);

Zipcode is 12000