RAISERROR (Transact-SQL)RAISERROR (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

產生錯誤訊息並起始工作階段的錯誤處理。Generates an error message and initiates error processing for the session. RAISERROR 可以參考儲存在 sys.messages 目錄檢視表的使用者自訂訊息,或是動態建立訊息。RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. 訊息以伺服器錯誤訊息傳回給呼叫應用程式,或傳回給 TRY...CATCH 建構的相關聯 CATCH 區塊。The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. 新應用程式應該改用 THROWNew applications should use THROW instead.

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

語法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
RAISERROR ( { msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]  

引數Arguments

msg_idmsg_id
這是使用 sp_addmessage 儲存在 sys.messages 目錄檢視表的使用者自訂錯誤訊息編號。Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. 使用者自訂錯誤訊息的錯誤號碼應該大於 50000。Error numbers for user-defined error messages should be greater than 50000. 如果未指定 msg_id,RAISERROR 會引發錯誤號碼為 50000 的錯誤訊息。When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

msg_strmsg_str
這是使用者自訂訊息,格式類似於 C 標準程式庫中的 printf 函數。Is a user-defined message with formatting similar to the printf function in the C standard library. 這個錯誤訊息最多可有 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. 例如,指派值為 2 的 %d 替代參數,實際上會在訊息字串中產生一個字元,但在內部卻也佔用了三個額外儲存字元。For example, the substitution parameter of %d with an assigned value of 2 actually produces one character in the message string but also internally takes up three additional characters of storage. 這項儲存需求減少了訊息輸出的可用字元數。This storage requirement decreases the number of available characters for message output.

當指定 msg_str 時,RAISERROR 會引發錯誤號碼為 50000 的錯誤訊息。When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

msg_str 是具有選擇性內嵌轉換規格的字元字串。msg_str is a string of characters with optional embedded conversion specifications. 每一轉換規格定義了引數清單中的值如何格式化,以及如何置入位於 msg_str 中轉換規格的欄位。Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. 轉換規格具有這個格式:Conversion specifications have this format:

% [[旗標] [寬度] [.% [[flag] [width] [. 有效位數] [{h | l}]] 類型precision] [{h | l}]] type

可用於 msg_str 中的參數有:The parameters that can be used in msg_str are:

旗標flag

這是決定替代值之間距與對齊的程式碼。Is a code that determines the spacing and justification of the substituted value.

程式碼Code 前置詞或對齊Prefix or justification DescriptionDescription
- (減號)- (minus) 靠左對齊Left-justified 給定欄位寬度內的引數值靠左對齊。Left-justify the argument value within the given field width.
+ (加號)+ (plus) 符號前置詞Sign prefix 如果值為帶正負號的類型,則在引數值前加上正號 (+) 或負號 (-)。Preface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (零)0 (zero) 零填補Zero padding 在輸出前加上 0,直到到達最小寬度為止。Preface the output with zeros until the minimum width is reached. 當 0 與減號 (-) 出現時,0 會被忽略。When 0 and the minus sign (-) appear, 0 is ignored.
# (數字)# (number) x 或 X 之十六進位類型的 0x 前置詞0x prefix for hexadecimal type of x or X 使用 o、x 或 X 格式時,數字符號 (#) 旗標會分別在非零值前面加上 0、0x 或 0X。When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. 當在 d、i 或 u 前面加上數字符號 (#) 旗標時,該旗標會被忽略。When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
' ' (空白)' ' (blank) 空間填補Space padding 如果輸出值帶正負號且為正值時,會在輸出值前加上空格。Preface the output value with blank spaces if the value is signed and positive. 如果包含了正號 (+) 旗標時,這個空格會被忽略。This is ignored when included with the plus sign (+) flag.

寬度width

這是定義引數值所在欄位最小寬度的整數。Is an integer that defines the minimum width for the field into which the argument value is placed. 如果引數值的長度等於或長於「寬度」,則列印出的值不帶填補。If the length of the argument value is equal to or longer than width, the value is printed with no padding. 如果值短於「寬度」,則會將值填補至「寬度」中指定的長度。If the value is shorter than width, the value is padded to the length specified in width.

星號 (*) 表示寬度是由引數清單中相關聯的引數所指定,必須是整數值。An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

有效位數precision

這是從字串值的引數值取得的最大字元數。Is the maximum number of characters taken from the argument value for string values. 例如,如果字串有五個字元而有效位數為 3,則只會使用字串值的前三個字元。For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

至於整數值,「有效位數」是列印出的最少小數位數。For integer values, precision is the minimum number of digits printed.

星號 (*) 表示有效位數是由引數清單中相關聯的引數所指定,必須是整數值。An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} 類型{h | l} type

這會搭配字元類型 d、i、o、s、x、X 或 u 一起使用,並建立 shortint (h) 或 longint (l) 值。Is used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

類型規格Type specification 表示Represents
d 或 id or i 帶正負號的整數Signed integer
oo 不帶正負號的八進位Unsigned octal
ss StringString
uu 不帶正負號的整數Unsigned integer
x 或 Xx or X 不帶正負號的十六進位Unsigned hexadecimal

注意

這些類型規格的基礎乃是原本為 C 標準程式庫中 printf 函數所定義的規格。These type specifications are based on the ones originally defined for the printf function in the C standard library. 用於 RAISERROR 訊息字串的類型規格對應到 Transact-SQLTransact-SQL 資料類型,而用於 printf 的規格對應到 C 語言資料類型。The type specifications used in RAISERROR message strings map to Transact-SQLTransact-SQL data types, while the specifications used in printf map to C language data types. Transact-SQLTransact-SQL 沒有類似於相關聯 C 資料類型的資料類型時,RAISERROR 不支援用於 printf 的類型規格。Type specifications used in printf are not supported by RAISERROR when Transact-SQLTransact-SQL does not have a data type similar to the associated C data type. 例如,RAISERROR 不支援指標的 %p 規格,因為 Transact-SQLTransact-SQL 沒有指標資料類型。For example, the %p specification for pointers is not supported in RAISERROR because Transact-SQLTransact-SQL does not have a pointer data type.

注意

若要轉換 Transact-SQLTransact-SQL bigint 資料型別的值,請指定 %I64dTo convert a value to the Transact-SQLTransact-SQL bigint data type, specify %I64d.

@local_variable*
這是任何有效字元資料類型的變數,其中包含採用與 msg_str 相同方法格式化的字串。Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str.
@local_variable* 必須是 charvarchar,或能隱含地轉換成這些資料型別。*@local_variable* must be char or varchar, or be able to be implicitly converted to these data types.

severityseverity
這是與這則訊息相關聯的使用者自訂嚴重性層級。Is the user-defined severity level associated with this message. 當利用 msg_id 來引發使用 sp_addmessage 建立的使用者自訂訊息時,RAISERROR 上指定的嚴重性會覆寫 sp_addmessage 中指定的嚴重性。When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage.

任何使用者皆可指定從 0 到 18 的嚴重性層級。Severity levels from 0 through 18 can be specified by any user. 從 19 到 25 的嚴重性層級只能由系統管理員 (sysadmin) 固定伺服器角色成員或具有 ALTER TRACE 權限的使用者來指定。Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. 因為從 19 到 25 的嚴重性層級需要 WITH LOG 選項。For severity levels from 19 through 25, the WITH LOG option is required. 小於 0 的嚴重性層級會被解譯為 0。Severity levels less than 0 are interpreted as 0. 大於 25 的嚴重性層級會被解譯為 25。Severity levels greater than 25 are interpreted as 25.

警告

從 20 到 25 的嚴重性層級是極嚴重的。Severity levels from 20 through 25 are considered fatal. 如果遇到嚴重的嚴重性層級,用戶端連接會在收到訊息之後中斷,而該錯誤會記錄在錯誤和應用程式記錄檔中。If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

您可以指定 -1,以傳回與錯誤相關聯的嚴重性值,如下列範例所示。You can specify -1 to return the severity value associated with the error as shown in the following example.

RAISERROR (15600,-1,-1, 'mysp_CreateCustomer');  

以下為結果集:Here is the result set.

Msg 15600, Level 15, State 1, Line 1   
An invalid parameter or option was specified for procedure 'mysp_CreateCustomer'.

statestate
這是介於 0 到 255 之間的整數。Is an integer from 0 through 255. 負值預設為 1。Negative values default to 1. 不應使用大於 255 的值。Values larger than 255 should not be used.

如果相同的使用者自訂錯誤在多個位置引發,針對每個位置使用唯一的狀態碼可以協助您找出引發錯誤的程式碼區段。If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

引數argument
這些參數可替代 msg_str 或對應於 msg_id 的訊息中所定義的 變數。Are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. 可以有 0 或更多的替代參數,但是替代參數的總數不能超過 20。There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. 每個替代參數都可以是區域變數或任何以下的這些資料類型:tinyintsmallintintcharvarcharncharnvarcharbinaryvarbinaryEach substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. 不支援其他資料類型。No other data types are supported.

選項option
這是錯誤的自訂選項,可以是下表中的值之一。Is a custom option for the error and can be one of the values in the following table.

ReplTest1Value DescriptionDescription
LOGLOG MicrosoftMicrosoft SQL ServerSQL Server Database EngineDatabase Engine 執行個體的錯誤記錄檔和應用程式記錄檔中記錄錯誤。Logs the error in the error log and the application log for the instance of the MicrosoftMicrosoft SQL ServerSQL Server Database EngineDatabase Engine. 記錄在錯誤記錄檔中的錯誤目前最大限制為 440 位元組。Errors logged in the error log are currently limited to a maximum of 440 bytes. 只有系統管理員 (sysadmin) 固定伺服器角色成員,或具有 ALTER TRACE 權限的使用者,才可以指定 WITH LOG。Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.

適用於:Applies to: SQL ServerSQL Server, SQL DatabaseSQL DatabaseSQL ServerSQL Server, SQL DatabaseSQL Database
NOWAITNOWAIT 立即傳送訊息給用戶端。Sends messages immediately to the client.

適用於:Applies to: SQL ServerSQL Server, SQL DatabaseSQL DatabaseSQL ServerSQL Server, SQL DatabaseSQL Database
SETERRORSETERROR 不論嚴重性層級為何,都將 @@ERROR 和 ERROR_NUMBER 值設定為 msg_id 或 50000。Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

適用於:Applies to: SQL ServerSQL Server, SQL DatabaseSQL DatabaseSQL ServerSQL Server, SQL DatabaseSQL Database

RemarksRemarks

由 RAISERROR 所產生的錯誤,運作方式和由 Database EngineDatabase Engine 程式碼所產生的錯誤相同。The errors generated by RAISERROR operate the same as errors generated by the Database EngineDatabase Engine code. 由 RAISERROR 指定的值是由 ERROR_LINE、ERROR_MESSAGE、ERROR_NUMBER、ERROR_PROCEDURE、ERROR_SEVERITY、ERROR_STATE 和 @@ERROR 等系統函數所報告。The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. 在 TRY 區塊以 11 或更高的嚴重性來執行 RAISERROR 時,RAISERROR 會傳送控制項到相關聯的 CATCH 區塊。When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. 如果在下列情況下執行 RAISERROR,會將錯誤傳回給呼叫端:The error is returned to the caller if RAISERROR is run:

  • 在任何 TRY 區塊的範圍外執行。Outside the scope of any TRY block.

  • 在 TRY 區塊以 10 或更低的嚴重性執行。With a severity of 10 or lower in a TRY block.

  • 以會結束資料庫連接的 20 或更高的嚴重性執行。With a severity of 20 or higher that terminates the database connection.

CATCH 區塊可以使用 RAISERROR 來重新擲出錯誤,這個錯誤可藉由使用 ERROR_NUMBER 和 ERROR_MESSAGE 等系統函數擷取原來的錯誤資訊,來叫用 CATCH 區塊。CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information. 對於嚴重性從 1 到 10 的訊息,@@ERROR 預設會設定為 0。@@ERROR is set to 0 by default for messages with a severity from 1 through 10.

msg_id 指定來自 sys.messages 目錄檢視表的使用者自訂訊息時,RAISERROR 在處理文字資料行的訊息時所使用的規則,會與套用至利用 msg_str 而指定之使用者定義訊息文字的規則相同。When msg_id specifies a user-defined message available from the sys.messages catalog view, RAISERROR processes the message from the text column using the same rules as are applied to the text of a user-defined message specified using msg_str. 使用者自訂訊息文字可以包含轉換規格,且 RAISERROR 會將引數值對應到轉換規格。The user-defined message text can contain conversion specifications, and RAISERROR will map argument values into the conversion specifications. 利用 sp_addmessage 來加入使用者自訂錯誤訊息,以及利用 sp_dropmessage 來刪除使用者自訂錯誤訊息。Use sp_addmessage to add user-defined error messages and sp_dropmessage to delete user-defined error messages.

RAISERROR 可以用作 PRINT 的替代方法,將訊息傳回給呼叫的應用程式。RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR 支援類似於 C 標準程式庫中 printf 函數功能的字元替代,而 Transact-SQLTransact-SQL PRINT 陳述式則不支援。RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQLTransact-SQL PRINT statement does not. PRINT 陳述式不受 TRY 區塊影響,而在 TRY 區塊中以嚴重性 11 到 19 執行的 RAISERROR 會將控制項傳送給相關聯的 CATCH 區塊。The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. 若要使用 RAISERROR 傳回來自 TRY 區塊的訊息,而不叫用 CATCH 區塊,請指定 10 或更低的嚴重性。Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

通常連續引數會取代連續轉換規格;第一個引數會取代第一個轉換規格,第二個引數會取代第二個轉換規格,依此類推。Typically, successive arguments replace successive conversion specifications; the first argument replaces the first conversion specification, the second argument replaces the second conversion specification, and so on. 例如,在下列 RAISERROR 陳述式中,N'number' 的第一個引數會取代 %s 的第一個轉換規格,而第二個引數 5 則會取代 %d. 的第二個轉換規格。For example, in the following RAISERROR statement, the first argument of N'number' replaces the first conversion specification of %s; and the second argument of 5 replaces the second conversion specification of %d.

RAISERROR (N'This is message %s %d.', -- Message text.  
           10, -- Severity,  
           1, -- State,  
           N'number', -- First argument.  
           5); -- Second argument.  
-- The message text returned is: This is message number 5.  
GO  

如果為轉換規格的寬度或有效位數指定星號 (*),則要用於寬度或有效位數的值會被指定成整數引數值。If an asterisk (*) is specified for either the width or precision of a conversion specification, the value to be used for the width or precision is specified as an integer argument value. 在這個情況下,一個轉換規格最多可使用三個引數,分別是寬度、有效位數和替代值。In this case, one conversion specification can use up to three arguments, one each for the width, precision, and substitution value.

例如,下列兩個 RAISERROR 陳述式都會傳回相同的字串。For example, both of the following RAISERROR statements return the same string. 一個在引數清單中指定寬度和有效位數值;另一個在轉換規格中指定寬度和有效位數值。One specifies the width and precision values in the argument list; the other specifies them in the conversion specification.

RAISERROR (N'<\<%*.*s>>', -- Message text.  
           10, -- Severity,  
           1, -- State,  
           7, -- First argument used for width.  
           3, -- Second argument used for precision.  
           N'abcde'); -- Third argument supplies the string.  
-- The message text returned is: <<    abc>>.  
GO  
RAISERROR (N'<\<%7.3s>>', -- Message text.  
           10, -- Severity,  
           1, -- State,  
           N'abcde'); -- First argument supplies the string.  
-- The message text returned is: <<    abc>>.  
GO  

範例Examples

A.A. 從 CATCH 區塊傳回錯誤資訊Returning error information from a CATCH block

下列程式碼範例顯示如何在 RAISERROR 區塊內使用 TRY,使執行位置跳到相關聯的 CATCH 區塊。The following code example shows how to use RAISERROR inside a TRY block to cause execution to jump to the associated CATCH block. 這個範例也會顯示如何利用 RAISERROR,來傳回叫用 CATCH 區塊之錯誤的相關資訊。It also shows how to use RAISERROR to return information about the error that invoked the CATCH block.

注意

RAISERROR 只會產生由 1 到 127 之狀態的錯誤。RAISERROR only generates errors with state from 1 through 127. 因為 Database EngineDatabase Engine 可能會引發狀態為 0 的錯誤,我們建議您在傳送 ERROR_STATE 的值給 RAISERROR 的狀態參數之前,先檢查它傳回的錯誤狀態。Because the Database EngineDatabase Engine may raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR.

BEGIN TRY  
    -- RAISERROR with severity 11-19 will cause execution to   
    -- jump to the CATCH block.  
    RAISERROR ('Error raised in TRY block.', -- Message text.  
               16, -- Severity.  
               1 -- State.  
               );  
END TRY  
BEGIN CATCH  
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
  
    SELECT   
        @ErrorMessage = ERROR_MESSAGE(),  
        @ErrorSeverity = ERROR_SEVERITY(),  
        @ErrorState = ERROR_STATE();  
  
    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );  
END CATCH;  

B.B. 在 sys.messages 中建立特定訊息Creating an ad hoc message in sys.messages

下列範例會顯示如何引發儲存在 sys.message 目錄檢視表的訊息。The following example shows how to raise a message stored in the sys.messages catalog view. 使用 sp_addmessage 系統預存程序,可將訊息加入 sys.messages 目錄檢視表中成為訊息編號 50005The message was added to the sys.messages catalog view by using the sp_addmessage system stored procedure as message number 50005.

sp_addmessage @msgnum = 50005,  
              @severity = 10,  
              @msgtext = N'<\<%7.3s>>';  
GO  
RAISERROR (50005, -- Message id.  
           10, -- Severity,  
           1, -- State,  
           N'abcde'); -- First argument supplies the string.  
-- The message text returned is: <<    abc>>.  
GO  
sp_dropmessage @msgnum = 50005;  
GO  

C.C. 使用區域變數來提供訊息文字Using a local variable to supply the message text

下列程式碼範例會顯示如何使用本機變數為 RAISERROR 陳述式提供訊息文字。The following code example shows how to use a local variable to supply the message text for a RAISERROR statement.

DECLARE @StringVariable NVARCHAR(50);  
SET @StringVariable = N'<\<%7.3s>>';  
  
RAISERROR (@StringVariable, -- Message text.  
           10, -- Severity,  
           1, -- State,  
           N'abcde'); -- First argument supplies the string.  
-- The message text returned is: <<    abc>>.  
GO  

另請參閱See Also

內建函數 (Transact-SQL) Built-in Functions (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
PRINT (Transact-SQL) PRINT (Transact-SQL)
sp_addmessage (Transact-SQL) sp_addmessage (Transact-SQL)
sp_dropmessage (Transact-SQL) sp_dropmessage (Transact-SQL)
sys.messages (Transact-SQL) sys.messages (Transact-SQL)
xp_logevent (Transact-SQL) xp_logevent (Transact-SQL)
@@ERROR (Transact-SQL) @@ERROR (Transact-SQL)
ERROR_LINE (Transact-SQL) ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL) ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL) ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL) ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL) ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL) ERROR_STATE (Transact-SQL)
TRY...CATCH (Transact-SQL)TRY...CATCH (Transact-SQL)