Export (0) Print
Expand All

FORMATMESSAGE (Transact-SQL)

Constructs a message from an existing message in sys.messages. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

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

msg_number

Is the ID of the message stored in sys.messages. If msg_number is <= 13000, or if the message does not exist in sys.messages, NULL is returned.

param_value

Is a parameter value for use in the message. Can be more than one parameter value. The values must be specified in the order in which the placeholder variables appear in the message. The maximum number of values is 20.

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

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.

The following example uses a replication message 20009 stored in sys.messages as, "The article '%s' could not be added to the publication '%s'." FORMATMESSAGE substitutes the values First Variable and Second Variable for the parameter placeholders. The resulting string, "The article 'First Variable' could not be added to the publication 'Second Variable'.", is stored in the local variable @var1.

SELECT text FROM sys.messages WHERE message_id = 20009 AND language_id = 1033;
DECLARE @var1 VARCHAR(200); 
SELECT @var1 = FORMATMESSAGE(20009, 'First Variable', 'Second Variable'); 
SELECT @var1;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft