title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
RAISERROR (Transact-SQL) |
RAISERROR (Transact-SQL) |
rwestMSFT |
randolphwest |
08/09/2022 |
sql |
t-sql |
reference |
|
|
TSQL |
>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current |
[!INCLUDE sql-asdb-asdbmi-asa-pdw]
[!NOTE]
TheRAISERROR
statement does not honorSET XACT_ABORT
. New applications should useTHROW
instead ofRAISERROR
.
Generates an error message and initiates error processing for the session. RAISERROR
can either reference a user-defined message stored in the sys.messages
catalog view, or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH
block of a TRY...CATCH
construct. New applications should use THROW instead.
:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: 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 Synapse Analytics and Parallel Data Warehouse:
RAISERROR ( { msg_str | @local_variable }
{ , severity, state }
[ , argument [ , ...n ] ] )
[ WITH option [ , ...n ] ]
[!INCLUDEsql-server-tsql-previous-offline-documentation]
Arguments
msg_id
A user-defined error message number stored in the sys.messages
catalog view using sp_addmessage
. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR
raises an error message with an error number of 50000.
msg_str
A user-defined message with formatting similar to the printf
function in the C standard library. The error message can have a maximum of 2,047 characters. 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. 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.
When msg_str is specified, RAISERROR
raises an error message with an error number of 50000.
msg_str is a string of characters with optional embedded conversion specifications. 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] [. precision] [{h | l}]] type
The parameters that can be used in msg_str are:
flag
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 (zero) | Zero padding | Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored. |
# (number) | 0x prefix for hexadecimal type of x or X | When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. 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
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
The maximum number of characters taken from the argument value for string values. 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} type
Used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.
Type specification | Represents |
---|---|
d or i | Signed integer |
o | Unsigned octal |
s | String |
u | Unsigned integer |
x or X | Unsigned hexadecimal |
These type specifications are based on the ones originally defined for the printf
function in the C standard library. The type specifications used in RAISERROR
message strings map to [!INCLUDEtsql] data types, while the specifications used in printf
map to C language data types. Type specifications used in printf
are not supported by RAISERROR
when [!INCLUDEtsql] does not have a data type similar to the associated C data type. For example, the %p specification for pointers is not supported in RAISERROR
because [!INCLUDEtsql] does not have a pointer data type.
To convert a value to the [!INCLUDEtsql] bigint data type, specify %I64d.
@local_variable
Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar, or be able to be implicitly converted to these data types.
severity
The user-defined severity level associated with this message. 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
.
For severity levels from 19 through 25, the WITH LOG option is required. Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.
[!CAUTION]
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.
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');
[!INCLUDEssResult]
Msg 15600, Level 15, State 1, Line 1
An invalid parameter or option was specified for procedure 'mysp_CreateCustomer'.
state
An integer from 0 through 255. Negative values default to 1. 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
The parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. 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.
option
A custom option for the error and can be one of the values in the following table.
Value | Description |
---|---|
LOG |
Logs the error in the error log and the application log for the instance of the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssDE]. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
[!INCLUDEapplies] [!INCLUDEssNoVersion] |
NOWAIT |
Sends messages immediately to the client.
[!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS] |
SETERROR |
Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.
[!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS] |
Remarks
The errors generated by RAISERROR
operate the same as errors generated by the [!INCLUDEssDE] code. 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. When RAISERROR
is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH
block. The error is returned to the caller if RAISERROR
is run:
-
Outside the scope of any
TRY
block. -
With a severity of 10 or lower in a
TRY
block. -
With a severity of 20 or higher that terminates the database connection.
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. @@ERROR
is set to 0 by default for messages with a severity from 1 through 10.
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. The user-defined message text can contain conversion specifications, and RAISERROR
will map argument values into the conversion specifications. Use sp_addmessage
to add user-defined error messages and sp_dropmessage
to delete user-defined error messages.
RAISERROR
can be used as an alternative to PRINT to return messages to calling applications. RAISERROR
supports character substitution similar to the functionality of the printf
function in the C standard library, while the [!INCLUDEtsql] PRINT
statement does not. 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. 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. 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.
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
Permissions
Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.
Examples
A. Returning error information from a CATCH block
The following code example shows how to use RAISERROR
inside a TRY
block to cause execution to jump to the associated CATCH
block. It also shows how to use RAISERROR
to return information about the error that invoked the CATCH
block.
[!NOTE]
RAISERROR
only generates errors with state from 1 through 127. Because the [!INCLUDEssDE] 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 ofRAISERROR
.
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. Creating an ad hoc message in sys.messages
The following example shows how to raise a message stored in the sys.messages catalog view. The message was added to the sys.messages catalog view by using the sp_addmessage
system stored procedure as message number 50005
.
EXEC 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 EXEC sp_dropmessage @msgnum = 50005; GO
C. Using a local variable to supply the message text
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
- Built-in Functions (Transact-SQL)
- DECLARE @local_variable (Transact-SQL)
- PRINT (Transact-SQL)
- sp_addmessage (Transact-SQL)
- sp_dropmessage (Transact-SQL)
- sys.messages (Transact-SQL)
- xp_logevent (Transact-SQL)
- @@ERROR (Transact-SQL)
- ERROR_LINE (Transact-SQL)
- ERROR_MESSAGE (Transact-SQL)
- ERROR_NUMBER (Transact-SQL)
- ERROR_PROCEDURE (Transact-SQL)
- ERROR_SEVERITY (Transact-SQL)
- ERROR_STATE (Transact-SQL)
- TRY…CATCH (Transact-SQL)
Summary: in this tutorial, you will learn how to use the SQL Server RAISERROR
statement to generate user-defined error messages.
If you develop a new application, you should use the THROW
statement instead.
SQL Server RAISEERROR
statement overview
The RAISERROR
statement allows you to generate your own error messages and return these messages back to the application using the same format as a system error or warning message generated by SQL Server Database Engine. In addition, the RAISERROR
statement allows you to set a specific message id, level of severity, and state for the error messages.
The following illustrates the syntax of the RAISERROR
statement:
Code language: SQL (Structured Query Language) (sql)
RAISERROR ( { message_id | message_text | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ];
Let’s examine the syntax of the RAISERROR
for better understanding.
message_id
The message_id
is a user-defined error message number stored in the sys.messages
catalog view.
To add a new user-defined error message number, you use the stored procedure sp_addmessage
. A user-defined error message number should be greater than 50,000. By default, the RAISERROR
statement uses the message_id
50,000 for raising an error.
The following statement adds a custom error message to the sys.messages
view:
Code language: SQL (Structured Query Language) (sql)
EXEC sp_addmessage @msgnum = 50005, @severity = 1, @msgtext = 'A custom error message';
To verify the insert, you use the following query:
Code language: SQL (Structured Query Language) (sql)
SELECT * FROM sys.messages WHERE message_id = 50005;
To use this message_id, you execute the RAISEERROR
statement as follows:
RAISERROR ( 50005,1,1)
Code language: SQL (Structured Query Language) (sql)
Here is the output:
Code language: SQL (Structured Query Language) (sql)
A custom error message Msg 50005, Level 1, State 1
To remove a message from the sys.messages
, you use the stored procedure sp_dropmessage
. For example, the following statement deletes the message id 50005:
Code language: SQL (Structured Query Language) (sql)
EXEC sp_dropmessage @msgnum = 50005;
message_text
The message_text
is a user-defined message with formatting like the printf
function in C standard library. The message_text
can be up to 2,047 characters, 3 last characters are reserved for ellipsis (…). If the message_text
contains 2048 or more, it will be truncated and is padded with an ellipsis.
When you specify the message_text
, the RAISERROR
statement uses message_id 50000 to raise the error message.
The following example uses the RAISERROR
statement to raise an error with a message text:
Code language: SQL (Structured Query Language) (sql)
RAISERROR ( 'Whoops, an error occurred.',1,1)
The output will look like this:
Whoops, an error occurred. Msg 50000, Level 1, State 1
Code language: SQL (Structured Query Language) (sql)
severity
The severity level is an integer between 0 and 25, with each level representing the seriousness of the error.
Code language: SQL (Structured Query Language) (sql)
0–10 Informational messages 11–18 Errors 19–25 Fatal errors
state
The state is an integer from 0 through 255. If you raise the same user-defined error at multiple locations, you can use a unique state number for each location to make it easier to find which section of the code is causing the errors. For most implementations, you can use 1.
WITH option
The option can be LOG
, NOWAIT
, or SETERROR
:
WITH LOG
logs the error in the error log and application log for the instance of the SQL Server Database Engine.WITH NOWAIT
sends the error message to the client immediately.WITH SETERROR
sets theERROR_NUMBER
and@@ERROR
values to message_id or 50000, regardless of the severity level.
SQL Server RAISERROR
examples
Let’s take some examples of using the RAISERROR
statement to get a better understanding.
A) Using SQL Server RAISERROR
with TRY CATCH
block example
In this example, we use the RAISERROR
inside a TRY
block to cause execution to jump to the associated CATCH
block. Inside the CATCH
block, we use the RAISERROR
to return the error information that invoked the CATCH
block.
Code language: SQL (Structured Query Language) (sql)
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT; BEGIN TRY RAISERROR('Error occurred in the TRY block.', 17, 1); END TRY BEGIN CATCH SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- return the error inside the CATCH block RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH;
Here is the output:
Code language: SQL (Structured Query Language) (sql)
Msg 50000, Level 17, State 1, Line 16 Error occurred in the TRY block.
B) Using SQL Server RAISERROR
statement with a dynamic message text example
The following example shows how to use a local variable to provide the message text for a RAISERROR
statement:
Code language: SQL (Structured Query Language) (sql)
DECLARE @MessageText NVARCHAR(100); SET @MessageText = N'Cannot delete the sales order %s'; RAISERROR( @MessageText, -- Message text 16, -- severity 1, -- state N'2001' -- first argument to the message text );
The output is as follows:
Code language: SQL (Structured Query Language) (sql)
Msg 50000, Level 16, State 1, Line 5 Cannot delete the sales order 2001
When to use RAISERROR
statement
You use the RAISERROR
statement in the following scenarios:
- Troubleshoot Transact-SQL code.
- Return messages that contain variable text.
- Examine the values of data.
- Cause the execution to jump from a
TRY
block to the associatedCATCH
block. - Return error information from the
CATCH
block to the callers, either calling batch or application.
In this tutorial, you will learn how to use the SQL Server RAISERROR
statement to generate user-defined error messages.
message_text — сообщение, которое вы хотите показать при ошибке. Замечание: вы можете добавлять пользовательские сообщения для вывода информации об ошибке. Смотрите следующий раздел статьи.
message_id — id сообщения об ошибке. Если вы хотите вывести пользовательское сообщение, вы можете определить этот идентификатор. Посмотрите список идентификаторов сообщений в sys.messages DMV.
Запрос:
select * from sys.messages
Вывод:
severity — серьезность ошибки. Тип данных переменной severity — smallint, значения находятся в диапазоне от 0 до 25. Допустимыми значениями серьезности ошибки являются:
- 0-10
- 11-18
- 19-25
— информационные сообщения
— ошибки
— фатальные ошибки
Замечание: Если вы создаете пользовательское сообщение, сложность, указанная в этом сообщении, будет перебиваться сложностью, заданной в операторе RAISERROR.
state — уникальное идентификационное число, которое может использоваться для раздела кода, вызывающего ошибку. Тип данных параметра state — smallint, и допустимые значения между 0 и 255.
Теперь давайте перейдем к практическим примерам.
Пример 1: использование оператора SQL Server RAISERROR для вывода сообщения
В этом примере вы можете увидеть, как можно отобразить ошибку или информационное сообщение с помощью оператора RAISERROR.
Предположим, что вы хотите отобразить сообщение после вставки записей в таблицу. Мы можем использовать операторы PRINT или RAISERROR. Ниже — код:
SET nocount ON
INSERT INTO tblpatients
(patient_id,
patient_name,
address,
city)
VALUES ('OPD00006',
'Nimesh Upadhyay',
'AB-14, Ratnedeep Flats',
'Mehsana')
RAISERROR ( 'Patient detail added successfully',1,1)
Вывод:
Как видно на рисунке выше, ID сообщения равно 50000, поскольку это пользовательское сообщение.
Пример 2: оператор SQL RAISERROR с текстом динамического сообщения
Теперь посмотрите, как мы можем создать текст динамического сообщения для оператора SQL RAISERROR.
Предположим, что мы хотим напечатать в сообщении ID пациента. Я описал локальную переменную с именем @PatientID, которая содержит patient_id. Чтобы отобразить значение переменной @PatientID в тексте сообщения, мы можем использовать следующий код:
DECLARE @PatientID VARCHAR(15)
DECLARE @message NVARCHAR(max)
SET @PatientID='OPD00007'
SET @message ='Patient detail added successfully. The OPDID is %s'
INSERT INTO tblpatients
(patient_id,
patient_name,
address,
city)
VALUES ('' + @PatientID + '',
'Nimesh Upadhyay',
'AB-14, Ratnedeep Flats',
'Mehsana')
RAISERROR ( @message,1,1,@patientID)
Вывод:
Для отображения строки в операторе RAISERROR, мы должны использовать операторы print в стиле языка Си.
Как видно на изображении выше, для вывода параметра в тексте сообщения я использую опцию %s, которая отображает строковое значение параметра. Если вы хотите вывести целочисленный параметр, вы можете использовать опцию %d.
Использование SQL RAISERROR в блоке TRY..CATCH
В этом примере мы добавляем SQL RAISERROR в блок TRY. При запуске этого кода он выполняет связанный блок CATCH. В блоке CATCH мы будем выводить подробную информацию о возникшей ошибке.
BEGIN try
RAISERROR ('Error invoked in the TRY code block.',16,1 );
END try
BEGIN catch
DECLARE @ErrorMsg NVARCHAR(4000);
DECLARE @ErrSeverity INT;
DECLARE @ErrState INT;
SELECT @ErrorMsg = Error_message(),
@ErrSeverity = Error_severity(),
@ErrState = Error_state();
RAISERROR (@ErrorMsg,
@ErrSeverity,
@ErrState
);
END catch;
Так мы добавили оператор RAISERROR с ВАЖНОСТЬЮ МЕЖДУ 11 И 19. Это вызывает выполнение блока CATCH.
В блоке CATCH мы показываем информацию об исходной ошибке, используя оператор RAISERROR.
Вывод:
Как вы можете увидеть, код вернул информацию об исходной ошибке.
Теперь давайте разберемся, как добавить пользовательское сообщение, используя хранимую процедуру sp_addmessage.
Хранимая процедура sp_addmessage
Мы можем добавить пользовательское сообщение, выполнив хранимую процедуру sp_addmessages. Синтаксис процедуры:
EXEC Sp_addmessage
@msgnum= 70001,
@severity=16,
@msgtext='Please enter the numeric value',
@lang=NULL,
@with_log='TRUE',
@replace='Replace';
@msgnum: задает номер сообщения. Тип данных параметра — integer. Это ID пользовательского сообщения.
@severity: указывает уровень серьезности ошибки. Допустимые значения от 1 до 25. Тип данных параметра — smallint.
@messagetext: задает текст сообщения, который вы хотите выводить. Тип данных параметра nvarchar(255), значение по умолчанию NULL.
@lang: задает язык, который вы хотите использовать для вывода сообщения об ошибке. Значение по умолчанию NULL.
@with_log: этот параметр используется для записи сообщения в просмотрщик событий. Допустимые значения TRUE и FALSE. Если вы задаете TRUE, сообщение об ошибке будет записано в просмотрщик событий Windows. Если выбрать FALSE, ошибка не будет записана в журнал ошибок Windows.
@replace: если вы хотите заменить существующее сообщение об ошибке на пользовательское сообщение и уровень серьезности, вы можете указать это в хранимой процедуре.
Предположим, что вы хотите создать сообщение об ошибке, которое возвращает ошибку о недопустимом качестве (invalid quality). В операторе INSERT значение invalid_quality находится в диапазоне между 20 и 100. Сообщение следует рассматривать как ошибку с уровнем серьезности 16.
Чтобы создать такое сообщение, выполните следующий запрос:
USE master;
go
EXEC Sp_addmessage
70001,
16,
N'Product Quantity must be between 20 and 100.';
go
После добавления сообщения выполните запрос ниже, чтобы увидеть его:
USE master
go
SELECT * FROM sys.messages WHERE message_id = 70001
Вывод:
Как использовать пользовательские сообщения об ошибках
Как упоминалось выше, мы должны использовать message_id в операторе RAISERROR для пользовательских сообщений.
Мы создали сообщение с ID = 70001. Оператор RAISERROR должен быть таким:
USE master
go
RAISERROR (70001,16,1 );
go
Вывод:
Оператор RAISERROR вернул пользовательское сообщение.
Хранимая процедура sp_dropmessage
Хранимая процедура sp_dropmessage используется для удаления пользовательских сообщений. Синтаксис оператора:
EXEC Sp_dropmessage @msgnum
Здесь @msgnum задает ID сообщения, которое вы хотите удалить.
Теперь мы хотим удалить сообщение, с ID = 70001. Запрос:
EXEC Sp_dropmessage 70001
Выполним следующий запрос для просмотра сообщения после его удаления:
USE master
go
SELECT * FROM sys.messages WHERE message_id = 70001
Вывод:
Как видно, сообщение было удалено.
Содержание
- RAISERROR (Transact-SQL)
- Syntax
- Arguments
- msg_id
- msg_str
- @local_variable
- severity
- state
- argument
- option
- Remarks
- Permissions
- Examples
- A. Returning error information from a CATCH block
- B. Creating an ad hoc message in sys.messages
- C. Using a local variable to supply the message text
- THROW (Transact-SQL)
- Синтаксис
- Аргументы
- Remarks
- Различия между инструкциями RAISERROR и THROW
- Примеры
- A. Использование инструкции THROW для вызова исключения
- Б. Использование инструкции THROW для повторного вызова исключения
- В. Использование инструкции FORMATMESSAGE с ключевым словом THROW
- Дальнейшие действия
RAISERROR (Transact-SQL)
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
The RAISERROR statement does not honor SET XACT_ABORT . New applications should use THROW instead of RAISERROR .
Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view, or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY. CATCH construct. New applications should use THROW instead.
Transact-SQL syntax conventions
Syntax
Syntax for SQL Server and Azure SQL Database:
Syntax for Azure Synapse Analytics and Parallel Data Warehouse:
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
msg_id
A user-defined error message number stored in the sys.messages catalog view using sp_addmessage . Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
msg_str
A user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. 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. 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.
When msg_str is specified, RAISERROR raises an error message with an error number of 50000.
msg_str is a string of characters with optional embedded conversion specifications. 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:
The parameters that can be used in msg_str are:
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 (zero) | Zero padding | Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored. |
# (number) | 0x prefix for hexadecimal type of x or X | When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. 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
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
The maximum number of characters taken from the argument value for string values. 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.
Used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.
Type specification | Represents |
---|---|
d or i | Signed integer |
o | Unsigned octal |
s | String |
u | Unsigned integer |
x or X | Unsigned hexadecimal |
These type specifications are based on the ones originally defined for the printf function in the C standard library. The type specifications used in RAISERROR message strings map to Transact-SQL data types, while the specifications used in printf map to C language data types. Type specifications used in printf are not supported by RAISERROR when Transact-SQL does not have a data type similar to the associated C data type. For example, the %p specification for pointers is not supported in RAISERROR because Transact-SQL does not have a pointer data type.
To convert a value to the Transact-SQL bigint data type, specify %I64d.
@local_variable
Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar, or be able to be implicitly converted to these data types.
severity
The user-defined severity level associated with this message. 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 .
For severity levels from 19 through 25, the WITH LOG option is required. Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 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.
You can specify -1 to return the severity value associated with the error as shown in the following example.
Here is the result set.
state
An integer from 0 through 255. Negative values default to 1. 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
The parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. 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.
option
A custom option for the error and can be one of the values in the following table.
Value | Description |
---|---|
LOG | Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
Applies to: SQL Server |
NOWAIT | Sends messages immediately to the client.
Applies to: SQL Server, SQL Database |
SETERROR | Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.
Applies to: SQL Server, SQL Database |
The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. 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. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:
Outside the scope of any TRY block.
With a severity of 10 or lower in a TRY block.
With a severity of 20 or higher that terminates the database connection.
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. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.
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. The user-defined message text can contain conversion specifications, and RAISERROR will map argument values into the conversion specifications. Use sp_addmessage to add user-defined error messages and sp_dropmessage to delete user-defined error messages.
RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. 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. 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. 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.
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.
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.
Permissions
Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.
Examples
A. Returning error information from a CATCH block
The following code example shows how to use RAISERROR inside a TRY block to cause execution to jump to the associated CATCH block. It also shows how to use RAISERROR to return information about the error that invoked the CATCH block.
RAISERROR only generates errors with state from 1 through 127. 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 .
The following example shows how to raise a message stored in the sys.messages catalog view. The message was added to the sys.messages catalog view by using the sp_addmessage system stored procedure as message number 50005 .
C. Using a local variable to supply the message text
The following code example shows how to use a local variable to supply the message text for a RAISERROR statement.
Источник
THROW (Transact-SQL)
Область применения: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)
Вызывает исключение и передает выполнение блоку CATCH конструкции TRY. CATCH.
Соглашения о синтаксисе Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
error_number
Константа или переменная, представляющая исключение. Аргумент error_number имеет тип int, должен иметь значение не меньше 50 000 и не больше 2 147 483 647.
message
Строка или переменная, описывающая исключение. Аргумент message имеет тип nvarchar(2048) .
state
Константа или переменная со значением в диапазоне от 0 до 255, указывающие состояние, которое должно быть связано с сообщением. Аргумент state имеет тип tinyint.
В инструкции, выполняемой до инструкции THROW, должен использоваться признак конца инструкции — точка с запятой (;).
Если конструкция TRY. CATCH недоступна, то пакет инструкций завершается. Задаются номер строки и процедура, где вызывается исключение. Серьезности задается значение 16.
Если инструкция THROW указана без параметров, то она должна находиться внутри блока CATCH. Результатом этого будет вызов возникшего исключения. Любая ошибка, возникающая в инструкции THROW, приводит к завершению пакета инструкций.
% является зарезервированным символом в тексте сообщения инструкции THROW, и его необходимо экранировать. Дважды укажите знак %, чтобы получить % в тексте сообщения, например: «Увеличение превышает 15 %% исходного значения.»
Различия между инструкциями RAISERROR и THROW
В следующей таблице приведены некоторые различия между инструкциями RAISERROR и THROW.
RAISERROR, инструкция | Инструкция THROW |
---|---|
Если инструкции RAISERROR передается параметр msg_id, то идентификатор должен быть задан в sys.messages. | Параметр error_number не требуется определять в sys.messages. |
Параметр msg_str может содержать стили форматирования printf. | Параметр message не принимает форматирование стиля printf. |
Параметр severity указывает серьезность исключения. | Параметр severity отсутствует. Если для создания исключения используется THROW, уровень серьезности всегда равен 16. Однако при использовании THROW для повторного создания существующего исключения для уровня серьезности устанавливается уровень серьезности этого исключения. |
SET XACT_ABORT не учитывается. | Если SET XACT_ABORT имеет значение ON, будет выполнен откат транзакций. |
Примеры
A. Использование инструкции THROW для вызова исключения
В следующем примере показано использование инструкции THROW для вызова исключения.
Б. Использование инструкции THROW для повторного вызова исключения
В следующем примере показано использование инструкции THROW для повторного вызова последнего исключения.
В. Использование инструкции FORMATMESSAGE с ключевым словом THROW
В следующем примере показано использование функции FORMATMESSAGE с ключевым словом THROW для вызова настроенных сообщений об ошибке. В данном примере сначала создается определяемое пользователем сообщение об ошибке с помощью sp_addmessage . Так как инструкция THROW не позволяет задать параметры подстановки для параметра message так, как это делает инструкция RAISERROR, для передачи трех значений параметров, ожидаемых при сообщении об ошибке 60000, используется функция FORMATMESSAGE.
Дальнейшие действия
Дополнительные сведения о связанных понятиях см. в следующих статьях:
Источник
Back to: SQL Server Tutorial For Beginners and Professionals
In this article, I am going to discuss how to raise errors explicitly in SQL Server with examples along with we will also discuss the different options that we can use with Raiserror in SQL Server. Please read our previous article where we discussed the real-time example of the Raise Error system function. As part of this article, we are going to discuss the following pointers.
- How to Raise Errors Explicitly in SQL Server?
- Raise error using RAISERROR statement in SQL Server
- Raise Error using throw statement in SQL Server
- What is the difference between the RAISERROR function and the throw statement?
- Understanding the RaiseError statement with the Log option.
- How to Raise Errors By storing the Error message in the SysMessage table.
How to Raise Errors Explicitly in SQL Server?
Generally, errors are raised in a program for predefined reasons like dividing a number by zero, violation of primary key, violation of check, violation of referential integrity, etc. But if you want then you can also raise an error in your programs in two different ways. They are as follows.
- Raiserror Statement
- throw Statement (new feature of SQL Server 2012)
Raiserror Syntax: Raiserror (errorid/errormsg, SEVERITY, state)[with log]
throw Syntax: Throw errorid, errormsg, state
Example: Raise Error using RAISERROR statement in SQL Server.
In the following stored Procedure, we raise an error when the division is 1 by using the RAISERROR statement.
CREATE PROCEDURE spDivideBy1(@No1 INT, @No2 INT) AS BEGIN DECLARE @Result INT SET @Result = 0 BEGIN TRY IF @No2 = 1 RAISERROR ('DIVISOR CANNOT BE ONE', 16, 1) SET @Result = @No1 / @No2 PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR) END TRY BEGIN CATCH PRINT ERROR_NUMBER() PRINT ERROR_MESSAGE() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() END CATCH END
Example of execution: EXEC spDivideBy1 10, 1
Example: Raise Error using throw statement in SQL Server.
The above procedure can also be rewritten with the help of a throw statement in place of Raiserror as following.
ALTER PROCEDURE spDivideBy2(@No1 INT, @No2 INT) AS BEGIN DECLARE @Result INT SET @Result = 0 BEGIN TRY IF @No2 = 1 THROW 50001,'DIVISOR CANNOT BE ONE', 1 SET @Result = @No1 / @No2 PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR) END TRY BEGIN CATCH PRINT ERROR_NUMBER() PRINT ERROR_MESSAGE() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() END CATCH END
EXECUTION: EXEC spDivideBy2 10, 1
What is the difference between the RAISERROR function and the throw statement in SQL Server?
If we use any of the two statements in a program for raising a custom error without try and catch blocks, the RAISERROR statement after raising the error will still continue the execution of the program whereas the throw statement will terminate the program abnormally on that line. But if they are used under try block both will behave in the same way that it will jump directly to catch block from where the error got raised.
The RAISERROR statement will give an option of specifying the ERROR SEVERITY Level of the error message whereas we don’t have this option in the case of the throw statement where all error messages will have a default ERROR SEVERITY level as 16.
In the case of RAISERROR, there is a chance of recording the error message into the server log file by using the with log option whereas we cannot do this in case of a throw statement.
In the case of throw, we need to specify both the errorid and error message to raise the error whereas in the case of RAISERROR we can specify either id or message. If the id is not specified default error id is 50000 but if we want to specify only the error id first we need to add the error message in the sysmessage table by specifying a unique id to the table.
OPTIONS WITH RAISERROR STATEMENT:
With Log: By using this option in the RAISERROR statement we can record the error message in the SQL Server log file so that if the errors are fatal database administrator can take care of fixing those errors. If the severity of the error is greater than 20 specifying the With Log option is mandatory. To test this ALTER the procedure spDivideBy1 by changing the raiserror statement as following
RAISERROR (‘DIVISOR CANNOT BE ONE’, 16, 1) WITH LOG
Below is the complete procedure
ALTER PROCEDURE spDivideBy1(@No1 INT, @No2 INT) AS BEGIN DECLARE @Result INT SET @Result = 0 BEGIN TRY IF @No2 = 1 RAISERROR ('DIVISOR CANNOT BE ONE', 16, 1) WITH LOG SET @Result = @No1 / @No2 PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR) END TRY BEGIN CATCH PRINT ERROR_NUMBER() PRINT ERROR_MESSAGE() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() END CATCH END
Now execute the procedure and whenever the given error raises we can watch the error messages recorded under the SQL Server log file. To view the log file. In object explorer, go to the management node, then open SQL Server logs node and open the current log file by double-clicking on it as shown below.
Using substitutional parameters in the error message of RAISERROR:
Just like C language, we can also substitute values into the error message to make the error message as dynamic as following
RAISERROR (‘THE NUMBER %d CANNOT BE DIVIDED BY %d’,16, 1, @No1, @No2)WITH LOG
Raising Errors By storing the Error message in the SysMessage table:
We can raise an error without giving the error message in the RAISERROR statement but in place of the error message we need the specify the error id and to specify the error id we need to record that error id with the error message in the SysMessage table by using the system defined procedure “SP_ADDMESSAGE”.
Syntax: SP_ADDMESSAGE <error id>, <severity>, <error message>
To test this first add a record to sysmessage table as following
EXEC sp_Addmessage 51000, 16, ‘DIVIDE BY ONE ERROR ENCOUNTERED’
Now alter the procedure by changing the RAISERROR statement as following
RAISERROR (51000,16, 1)WITH LOG
Deleting the error messages from sysmessages table:
Syntax: SP_DROPMESSAGE <error id>
Example: EXEC sp_dropMessage 51000
In the next article, I am going to discuss the try-catch implementation in SQL Server to handle the error in SQL Server. Here, in this article, I try to explain How to raise errors explicitly in SQL Server step by step with some examples.