RAISERROR (T-SQL)

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. The maximum value for msg_id is 2,147,483,647 (2 (31) - 1).

msg_str

Is an ad hoc message with formatting similar to the PRINTF format style used in C. The error message can have as many as 8,000 characters. 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 + or - 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 + 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 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 19 through 25 are used only by members of the sysadmin fixed server role. For severity levels 19 through 25, the WITH LOG option is required.

Caution Severity levels 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 of 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)

C. Use SETERROR with ad hoc and system messages

The first RAISERROR returns an @@ERROR value of 50000. The second returns the syntax error message used by Microsoft® SQL Server™ with an @@ERROR value of 101.

RAISERROR('Test Only', 1, 2) WITH SETERROR

SELECT @@ERROR

RAISERROR (101, 1, 2) WITH SETERROR

SELECT @@ERROR

Here is the result set:

Msg 50000, Level 1, State 50000

Test Only

-----------

50000

(1 row(s) affected)

Msg 101, Level 1, State 101

Line 0: SQL syntax error.