RAISERROR Statement

For SQL Server 6.5 information, see RAISERROR Statement in the What's New for SQL Server 6.5.

Returns a user-defined error message and sets a system flag to record that an error has occurred. RAISERROR lets the client retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. Once defined, this message is sent back to the client as a server error message.

The RAISERROR statement has been updated in SQL Server 6.0. The earlier syntax structure is supported for compatibility.

Syntax

RAISERROR ({msg_id | msg_str}, severity, state
[, argument1 [, argument2]] )
[WITH LOG]

where

msg_id

Is a user-defined error message stored in the sysmessages table. To add user-defined error messages, use sp_addmessage system stored procedure. To delete user-defined error messages, use sp_dropmessage system stored procedure. If a sysmessages error is used and the message was created using the format shown for msg_str, then supplied arguments (argument1, argument2, and so on) will be passed to the message of the supplied msg_id. Error numbers for user-defined error messages should be greater than 50,000. Ad hoc messages will raise an error of 50,000. The maximum value is 2,147,483,647 (2 (31) - 1).

When an error is raised, the error number is placed in the global variable @@ERROR, which stores the error number most recently generated by the system.

msg_str

Is an ad hoc message with formatting similar to the C PRINTF format style. The error message can have as many as 255 characters. All ad hoc messages will have one standard message ID of 14000.

The following format is supported for msg_str:

% [[flag] [width] [precision] [{h | l}]] type

where

flag

Determines the spacing and justification.

Code	Prefix or Justification	Meaning
- (minus)	Left-justified	Left-justify the result within the given field width.
(plus)	(plus) or - (minus) prefix	Preface the output value with a sign (+ or -) 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. If 0 and - appear, 0 is ignored. If 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 will be ignored if included with the + flag.

width

Defines the minimum width. An asterisk (*) allows the following argument to determine the width.

precision

Specifies the maximum number of characters printed for the output field or the minimum number of digits printed for integer values. An asterisk (*) allows the argument to determine the precision.

{h | 1} type

Used with types d, i, o, x, X, or u, creates short int (h) or long int values (l).

Type	Represents
d or i	Signed integer
o	Unsigned octal
p	Pointer
s	String
u	Unsigned integer
x or X	Unsigned hexadecimal

Note that float, double-, and single-character types are not supported.

severity

Represents the user-defined severity level associated with this message. Severity levels from 0 through 18 can be used by any user. For severity levels 19 through 25, the WITH LOG option is required. Only the system administrator can issue RAISERROR with a severity level of 19 through 25. For a list of SQL Server error severity levels, see the Microsoft SQL Server Administrator's Companion

Caution For a severity of 19 through 25, which are considered "fatal," the client connection will be terminated after receiving the message and the error will be logged in the error log and the event log.

state

Is an integer value from 1 through 127 that represents information about the invocation state of the error. A negative value for state will default to 1.

argument

Defines the parameters used in the substitution for variables defined in the msg_str or the message corresponding to the msg_id. There can be zero 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 datatypes: int1, int2, int4, char, varchar, binary, or varbinary. No other datatypes are supported.

WITH LOG

Logs the error in the server error log and the event log. This option is required for messages with a severity of 19 through 25, and it can be issued only by the system administrator. The xp_eventlog extended stored procedure provides similar functionality.

Examples

A.    Ad Hoc Messages

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_lvl = 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.    Messages Raised from sysmessages

This example shows how the employee_insupd trigger could achieve the same results using parameters passed to a message stored in the sysmessages table. The message was added to the sysmessages table with the sp_addmessage system stored procedure as message number 50005.

RAISERROR (50005, 16, -1, @@job_id, @@min_lvl, @@max_lvl)

See Also