RAISERROR Statement (version 6.5)

Sets @@ERROR to 0 by default for severity one through ten messages. The WITH SETERROR option sets the @@ERROR value to the msg_id argument or 50000 regardless of the severity.

For additional syntax information for the RAISERROR statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

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

where

msg_id

Is a user-defined error message stored in the sysmessages table.

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 50000.

severity

Is the user-defined severity level associated with this message. Severity levels 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.

state

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

argument

Specifies the parameter used in the substitution of 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 options

Is one of the following values:

options	Description
LOG	Logs the error in the server error log and the event log. This option is required for messages with a severity level of 19 through 25, and it can be issued only by the system administrator.
NOWAIT	Sends messages immediately to the client server.
SETERROR	Sets @@ERROR value to msg_id or 50000 regardless of the severity level.

Remarks

This statement now sets @@ERROR to 0 if the severity is between 1 and 10 inclusive. Messages with severity levels 10 and under are not errors, but they do provide additional information.

If you set the msg_id by using the SETERROR option, the RAISERROR statement assigns the msg_id to the @@ERROR regardless of severity.

Otherwise, other behaviors of the RAISERROR statement remain the same.

Important In SQL Server version 6.0, for messages with severity levels 10 and under, @@ERROR is set to 50000. To revert to SQL Server version 6.0 behavior, use either the SETERROR option or trace flag 2701.

For more information about trace flag 2701, see Trace Flags .

Examples

A.    Use msg_str

This example returns an @@ERROR value of 50000.

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

B.    Use msg_id

This example returns an @@ERROR value of 101.

RAISERROR (101, 1, 2) WITH SETERROR