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

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是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] [.% [[flag] [width] [. precision] [{h | l}]] typeprecision] [{h | l}]] type

可在 msg_str 中使用的参数包括:The parameters that can be used in msg_str are:

flagflag

用于确定被替换值的间距和对齐的代码。Is a code that determines the spacing and justification of the substituted value.

代码Code 前缀或对齐Prefix or justification 描述Description
-(减号)- (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 在达到最小宽度之前在输出前面加上零。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.

widthwidth

定义放置参数值的字段的最小宽度的整数。Is an integer that defines the minimum width for the field into which the argument value is placed. 如果参数值的长度等于或大于 width,则打印该值,无需进行填充。If the length of the argument value is equal to or longer than width, the value is printed with no padding. 如果该值小于 width,则将该值填充到 width 中指定的长度。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.

对于整数值,precision 是指打印的最小位数。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} type{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. 例如,Transact-SQLTransact-SQL 没有指针数据类型,因此 RAISERROR 不支持用于指针的 %p 规范。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 数据类型,请指定 %I64d。To 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 必须为 char 或 varchar,或者能够隐式转换为这些数据类型。
@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. 只有 sysadmin 固定服务器角色成员或具有 ALTER TRACE 权限的用户才能指定 19 到 25 之间的严重级别。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.

argumentargument
用于代替 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. 每个替换参数可以是本地变量或下列任何数据类型:tinyint、smallint、int、char、varchar、nchar、nvarchar、binary 或 varbinary。Each 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.

optionoption
错误的自定义选项,可以是下表中的任一值。Is a custom option for the error and can be one of the values in the following table.

ReplTest1Value 描述Description
LOGLOG MicrosoftMicrosoft SQL ServerSQL Server 数据库引擎Database Engine实例的错误日志和应用程序日志中记录错误。Logs the error in the error log and the application log for the instance of the MicrosoftMicrosoft SQL ServerSQL Server 数据库引擎Database 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 ServerSQL DatabaseSQL DatabaseSQL ServerSQL Server, SQL DatabaseSQL Database
NOWAITNOWAIT 将消息立即发送给客户端。Sends messages immediately to the client.

适用范围:Applies to: SQL ServerSQL ServerSQL 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 ServerSQL DatabaseSQL DatabaseSQL ServerSQL Server, SQL DatabaseSQL Database

RemarksRemarks

RAISERROR 生成的错误与数据库引擎Database Engine代码生成的错误的运行方式相同。The errors generated by RAISERROR operate the same as errors generated by the 数据库引擎Database 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. 当 RAISERROR 在严重级别为 11 或更高的情况下在 TRY 块中运行,它便会将控制传输至关联的 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.

  • 在严重级别为 10 或更低的情况下在 TRY 块中运行。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 来再次引发调用 CATCH 块的错误,方法是使用 ERROR_NUMBER 和 ERROR_MESSAGE 之类的系统函数检索原始错误消息。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 块的影响,而在严重级别为 11 到 19 的情况下在 TRY 块中运行的 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. 指定严重级别为 10 或更低以使用 RAISERROR 返回 TRY 块中的消息,而不必调用 CATCH 块。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 Engine可能引发状态为 0 的错误,因此,建议您先检查由 ERROR_STATE 返回的错误状态,然后将它作为一个值传递给状态参数 RAISERROR。Because the 数据库引擎Database 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.messages 目录视图中存储的消息。The following example shows how to raise a message stored in the sys.messages catalog view. 该消息通过 sp_addmessage 系统存储过程,以消息号 50005 添加到 sys.messages 目录视图中。The 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)