RAISERROR
Returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message.
Syntax
RAISERROR ( { msg_id | msg_str } { , severity , state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Arguments
msg_id
Is a user-defined error message stored in the sysmessages table. Error numbers for user-defined error messages should be greater than 50,000. Ad hoc messages raise an error of 50,000.
msg_str
Is an ad hoc message with formatting similar to the PRINTF format style used in C. The error message can have up to 400 characters. If the message contains more than 400 characters, only the first 397 will be displayed and an ellipsis will be added to indicate that the message has been cut. All ad hoc messages have a standard message ID of 14,000.
This format is supported for msg_str:
% [[flag] [width] [precision] [{h | l}]] type
The parameters that can be used in msg_str are:
flag
Is a code that determines the spacing and justification of the user-defined error message.
| Code | Prefix or justification | Description |
|---|---|---|
| - (minus) | Left-justified | Left-justify the result within the given field width. |
| + (plus) | + (plus) or - (minus) prefix | Preface the output value with a plus (+) or minus (-) sign if the output value is of signed type. |
| 0 (zero) | Zero padding | If width is prefaced with 0, zeros are added until the minimum width is reached. When 0 and - appear, 0 is ignored. When 0 is specified with an integer format (i, u, x, X, o, d), 0 is ignored. |
| # (number) | 0x prefix for hexadecimal type of x or X | When used with the o, x, or X format, the # flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the # 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 defining the minimum width. An asterisk (*) allows precision to determine the width.
precision
Is the maximum number of characters printed for the output field or the minimum number of digits printed for integer values. An asterisk (*) allows argument to determine the precision.
{h | l} type
Is used with character types d, i, o, x, X, or u, and creates short int (h) or long int (l) values.
| Character type | Represents |
|---|---|
| d or I | Signed integer |
| o | Unsigned octal |
| p | Pointer |
| s | String |
| u | Unsigned integer |
| x or X | Unsigned hexadecimal |
Note The float, double-, and single character types are not supported.
severity
Is the user-defined severity level associated with this message. Severity levels from 0 through 18 can be used by any user. Severity levels from 19 through 25 are used only by members of the sysadmin fixed server role. For severity levels from 19 through 25, the WITH LOG option is required.
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 log and the application log.
state
Is an arbitrary integer from 1 through 127 that represents information about the invocation state of the error. A negative value for state defaults to 1.
argument
Is 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; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4, char, varchar, binary, or varbinary. No other data types are supported.
option
Is a custom option for the error. option can be one of these values.
| Value | Description |
|---|---|
| LOG | Logs the error in the server error log and the application log. Errors logged in the server error log are currently limited to a maximum of 440 bytes. |
| NOWAIT | Sends messages immediately to the client. |
| SETERROR | Sets @@ERROR value to msg_id or 50000, regardless of the severity level. |
Remarks
If a sysmessages error is used and the message was created using the format shown for msg_str, the supplied arguments (argument1, argument2, and so on) are passed to the message of the supplied msg_id.
When you use RAISERROR to create and return user-defined error messages, use sp_addmessage to add user-defined error messages and sp_dropmessage to delete user-defined error messages.
When an error is raised, the error number is placed in the @@ERROR function, which stores the most recently generated error number. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.
Examples
A. Create an ad hoc message
This example shows two errors that can be raised. The first is a simple error with a static message. The second error is dynamically built based on the attempted modification.
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @@MIN_LVL tinyint,
@@MAX_LVL tinyint,
@@EMP_LVL tinyint,
@@JOB_ID smallint
SELECT @@MIN_LVl = min_lvl,
@@MAX_LV = max_lvl,
@@ EMP_LVL = i.job_lvl,
@@JOB_ID = i.job_id
FROM employee e, jobs j, inserted i
WHERE e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@@JOB_ID = 1) and (@@EMP_lVl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT @@ EMP_LVL BETWEEN @@MIN_LVL AND @@MAX_LVL)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @@JOB_ID, @@MIN_LVL, @@MAX_LVL)
ROLLBACK TRANSACTION
END
B. Create an ad hoc message in sysmessages
This example shows how to achieve the same results with RAISERROR using parameters passed to a message stored in the sysmessages table by executing the employee_insupd trigger. The message was added to the sysmessages table with the sp_addmessage system stored procedure as message number 50005.
Note This example is shown for illustration only.
RAISERROR (50005, 16, 1, @@JOB_ID, @@MIN_LVL, @@MAX_LVL)