FORMATMESSAGE (Transact-SQL)FORMATMESSAGE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

從 sys.messages 中現有的訊息或從提供的字串建構訊息。Constructs a message from an existing message in sys.messages or from a provided string. FORMATMESSAGE 的功能類似於 RAISERROR 陳述式的功能。The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. 不過,RAISERROR 會立即列印訊息,FORMATMESSAGE 則會傳回格式化的訊息,以便進一步處理。However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

msg_numbermsg_number
這是 sys.messages 中所儲存之訊息的識別碼。Is the ID of the message stored in sys.messages. 如果 msg_number 是 <= 13000,或 sys.messages 中沒有這則訊息,就會傳回 NULL。If msg_number is <= 13000, or if the message does not exist in sys.messages, NULL is returned.

msg_stringmsg_string
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)目前版本)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

這是括在單引號中的字串,且包含參數值預留位置。Is a string enclosed in single quotes and containing parameter value placeholders. 這個錯誤訊息最多可有 2,047 個字元。The error message can have a maximum of 2,047 characters. 如果訊息包含 2,048 個或更多字元,則只會顯示前 2,044 個字元,並且會加上省略符號以表示該訊息已被截斷。If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated. 請注意,由於內部儲存行為的緣故,替代參數比輸出顯示耗用更多字元。Note that substitution parameters consume more characters than the output shows because of internal storage behavior. 如需有關訊息字串結構以及字串中參數用法的資訊,請參閱 RAISERROR (Transact-SQL)msg_str 引數的說明。For information about the structure of a message string and the use of parameters in the string, see the description of the msg_str argument in RAISERROR (Transact-SQL).

param_valueparam_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. 最多可有 20 個值。The maximum number of values is 20.

傳回類型Return Types

nvarcharnvarchar

RemarksRemarks

如同 RAISERROR 陳述式,FORMATMESSAGE 會用所提供的參數值來替代訊息中的預留位置變數,以編輯訊息。Like the RAISERROR statement, FORMATMESSAGE edits the message by substituting the supplied parameter values for placeholder variables in the message. 如需有關錯誤訊息所允許之預留位置及編輯處理的詳細資訊,請參閱 RAISERROR (Transact-SQL)For more information about the placeholders allowed in error messages and the editing process, see RAISERROR (Transact-SQL).

FORMATMESSAGE 會用使用者目前的語言來查閱訊息。FORMATMESSAGE looks up the message in the current language of the user. 如果沒有當地語系化版本的訊息,就會使用 U.S. English 版本。If there is no localized version of the message, the U.S. English version is used.

如果是當地語系化的訊息,提供的參數值必須對應於 U.S. English 版本中的參數預留位置英文版。For localized messages, the supplied parameter values must correspond to the parameter placeholders in the U.S. English version. 即當地語系化版本中的參數 1 必須對應於 U.S. English 版本中參數 1。參數 2 必須對應於參數 2,依此類推。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.

範例Examples

A.A. 具有訊息編號的範例Example with a message number

當「發行項 '%s' 無法加入到發行集 '%s' 中」時,下列範例會使用儲存在 sys.messages 中的複寫訊息 20009。FORMATMESSAGE 會將 First VariableSecond Variable 這兩個值代入參數預留位置中。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. 產生的字串「發行項 'First Variable' 無法加入到發行集 'Second Variable' 中」會儲存在區域變數 @var1 中。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;  

B.B. 具有訊息字串的範例Example with a message string

適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)目前版本)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

下列範例會採用字串作為輸入。The following example takes a string as an input.

SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS Result;  

傳回:This is the first variable and this is the second variable.Returns: This is the first variable and this is the second variable.

C.C. 其他訊息字串格式範例Additional message string formatting examples

下列範例顯示各種格式選項。The following examples show a variety of formatting options.

SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with up to 3 leading zeros %03i', 5);  
SELECT FORMATMESSAGE('Signed int with up to 20 leading zeros %020i', 5);  
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);  
SELECT FORMATMESSAGE('Bigint %I64d', 3000000000);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);  
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);  
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);  
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);  
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);  
SELECT FORMATMESSAGE('Hello %s!', 'TEST');  
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');  
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');  

另請參閱See Also

RAISERROR (Transact-SQL)RAISERROR (Transact-SQL)
THROW (Transact-SQL) THROW (Transact-SQL)
sp_addmessage (Transact-SQL) sp_addmessage (Transact-SQL)
sys.messages (Transact-SQL) sys.messages (Transact-SQL)
CONCAT (Transact-SQL)CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)CONCAT_WS (Transact-SQL)
QUOTENAME (Transact-SQL)QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)REPLACE (Transact-SQL)
REVERSE (Transact-SQL)REVERSE (Transact-SQL)
STRING_AGG (Transact-SQL)STRING_AGG (Transact-SQL)
STRING_ESCAPE (Transact-SQL)STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)STUFF (Transact-SQL)
TRANSLATE (Transact-SQL)TRANSLATE (Transact-SQL)
系統函數 (Transact-SQL)System Functions (Transact-SQL)