FORMATMESSAGE function constructs a message from an existing message in sysmessages.
FORMATMESSAGE edits the message by substituting the supplied parameter values for placeholder variables in the message.
FORMATMESSAGE looks up the message in the current language of the user. If there is no localized version of the message, the U.S. English version is used.
Syntax of FORMATMESSAGE Function :
FORMATMESSAGE ( msg_number, param_value [ ,...n ] )
msg_number is error id of the message stored in sysmessages system table. It returns null when error id is not exists in sysmessages table.
param_value is one or more parameter values for use in the message. The values must be specified in the order in which the placeholder variables appear in the message. The maximum number of values is 20.
Return type of FORMATMESSAGE function is a nvarchar.
Examples of FORMATMESSAGE Function :
Example 1 : Use of FORMATMESSAGE function in select clause
SELECT FORMATMESSAGE(106,15)
Output
Too many table names in the query. The maximum allowable is 15.
Above example returns formatted error message by substituting the value 15 for the parameter placeholder.