FORMATMESSAGE (T-SQL)

Constructs a message from an existing message in sysmessages. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement; however, RAISERROR prints the message immediately, and FORMATMESSAGE returns the edited message for further processing.

Syntax

FORMATMESSAGE(msg_number, param_value[,...n])

Arguments

msg_number

Is the ID of the message that is stored in sysmessages. If the message does not exist in sysmessages, an error is returned.

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 Types

nvarchar

Remarks

Like the RAISERROR statement, FORMATMESSAGE edits the message by substituting the supplied parameter values for placeholder variables in the message. For information about the placeholders allowed in error messages and the editing process, see RAISERROR.

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.

For localized messages, the supplied parameter values must correspond to the parameter placeholders in the U.S. English version. That is, parameter 1 in the localized version must correspond to parameter 1 in the U.S. English version, parameter 2 must correspond to parameter 2, and so on.

Example:

This example uses a hypothetical message 50001, stored in sysmessages as “The number of rows in %s is %1d.” FORMATMESSAGE substitutes the values Table1 and 5 for the parameter placeholders. The resulting string, “The number of rows in Table1 is 5.” is stored in the local variable @var1.

DECLARE @var1 VARCHAR(100)

SELECT @var1 = FORMATMESSAGE(50001, 'Table1', 5)

See Also