Using FormatMessage function you can construct a message string located in sys.messages. The functionality of FORMATMESSAGE is very much same as RAISERROR statement. FORMATMESSAGE functions edits the message by substituting the supplied parameter values for placeholder variables in the message string.
Syntax:
FORMATMESSAGE ( { msg_number | ‘ msg_string ‘ } , [ param_value [ ,…n ] ] )
Msg_number: The is the Id column value of sys.,messages. This function will return Null if msg_number is less than 13000 or if the message doesn’t exist.
Msg_string : Now in SQL Server 2016 it enables you to pass your own message construct. You can now pass a string as well.
param_value:
It hold parameter value for use in the message. It Can be more than one parameter value. The value must be specified in the order in which place holders are placed.
The maximum length of msg_string is 2047 characters. If the message contains more than 2047 charcaters, then It will display only first 2044 characters and will add an ellipsis. Maximum 20 parameters can be passed to it.
It looks up the message in the current language of the user, if it doesn’t find any localized version of the message then it will use US English version.
In your message string the place holder should match with the type of value you wanted to show otherwise it will throw an error.
Example :
To show a string and an integer value, we need to use correct placeholder as per the data type.
Declare @v1 varchar(50) ='ABC', @v2 int =123
SELECT FORMATMESSAGE('String value %s. ', @v1) AS message
SELECT FORMATMESSAGE('Integer value %i. ', @v2) AS message
The variable you pass if doesn’t supported by the placeholder then it will throw an error.
FormatMessage function useful to write your own customize message.
