Raiserror error-number [format-String] [, Arg-list]

Reproduced to: http://hi.baidu.com/software_2008/blog/item/7d768731dde7ee11eac4af16.html

System pre-defined error code

SQL Server has 3831 predefined error codes, which are maintained in the master. dbo. sysmessages table. Each error code has a corresponding level and description.
The error definition level ranges from 0 to 25. More than 20 errors indicate major errors, which usually means that the storage process is terminated immediately and all customer connections must be reinitialized.
Non-critical errors only disable the currently running program line and continue execution.

How to determine whether an error occurs and obtain the error description:

IF @ ERROR = 0

Begin

SELECT @ ERRORMESSAGETXT = description FROM master. dbo. sysmessages WHERE error =@@ ERROR

End

2. raiserror syntax

Msg_id	Customize the message error code .. Raiserror accepts any number greater than 13000, but customizes the informationMsg_idIt must be greater than or equal to 50000.
Msg_str	The text of custom information.
Severity	The level of custom information. From 0 to 25, 19-25 is a major error code.
State	Displays the error status, which is not used in SQL.
Argument	Defines the value that can be replaced in the error message.
WITH...	There are three options: ·WITH LOGRecord error. It can only be used for errors with a level higher than 19. ·WITH NOWAITSend the error to the client immediately ·With seterrorSets @ ERROR to the value specifiedMsg_id, Regardless of severity level.

Syntax
RAISERROR ({msg_id =msg_str }{, severity, state}
[, Argument [,... n])
[With option [,... n]

Parameters
Msg_id

User-Defined error messages stored in the sysmessages table. The error number of the User-Defined error message must be greater than 50,000. Error 50,000 is generated by special messages.

Msg_str

Is a special message. Its format is similar to the printf format used in C. This error message can contain a maximum of 400 characters. If the information contains more than 400 characters,
Only the first 397 entries are displayed, and a ellipsis is added to indicate that the information has been truncated. The Standard Message ID of all specific messages is 14,000.

Msg_str supports the following format:

% [[Flag] [width] [precision] [{H ¦ L}] Type

Parameters that can be used in msg_str include:

Flag

Code used to determine the spacing and alignment of user-defined error messages.

{H | L}Type

Used with character types D, I, O, X, X, or u to createShort Int(H) orLong Int(L) type value.

Character Type	Indicates
D Or I	Signed integer
O	Unsigned octal number
P	Pointer type
S	String
U	Unsigned integer
X or X	Unsigned hexadecimal number

DescriptionNot SupportedFloat,Double-precision and single-precision character types.

Severity

Severity Level of message association defined by the user. You can use a severity level ranging from 0 to 18. The severity levels between 19 and 25 can only be used by members of SysAdmin fixed server roles.
To use a severity level between 19 and 25, You must select the with log option.

Note that the severity levels between 20 and 25 are considered fatal. In case of a fatal severity level, the client connection ends after receiving the message and records the error in the error log and Application Log.

State

Any integer ranging from 1 to 127 indicates the error call status. The negative value of state is 1 by default.

Argument

Is used to replace the variables defined in msg_str or replace the parameters of messages 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. Other data types are not supported.

Option

Incorrect custom options. Option can be one of the following values:

Value description
LOG records errors to server error logs and application logs. Currently, the error logs recorded in server error logs are limited to a maximum of 440 bytes.
NOWAIT sends the message to the client immediately.
SETERROR sets the value of @ ERROR to msg_id or 50000, regardless of the severity level.

3. custom error handling

IF (@ role_type_id is null)
BEGIN
RAISERROR ('parameter ''' role _ type_id ''can not be null. ', 16, 1) WITH NOWAIT
RETURN 1
END

Iv. custom error messages

Of course, in most cases, the error message will be used multiple times. It is obviously irrational to input the same information once again.
UseSp_addmessageStored Procedures add custom error messagesSysmessagesTable. Later, you can reference the new information ID number when using RAISERROR.

The ID number of the custom error message must be greater than or equal to 50000. The length of the custom error message cannot exceed 255 characters.
The following is an example. Create a New Information and assign the identification number 55555 with a level of 10. The call method is as follows: sp_addmessage 55555, 10, 'new error message .'
You can add a new error in the storage process: RAISERROR 55555, 10

Description

This statement is used to send error signals and messages to clients.

Syntax

RAISERROR Error-number[Format-string] [,Arg-list]

Parameters

Error-NumberError-NumberIs an integer greater than 17000. The error code is stored in global variables.@ Error.
Format-stringIf notFormat-stringIf this parameter is set to null, an error code is used to search for error messages in the system table. The range of messages that Adaptive Server Enterprise obtains from the SYSMESSAGES table is 17000-19999. In Adaptive Server Anywhere, this table is in an empty view, so error messages within this range should provide format strings. An error message numbered 20000 or greater is obtained from the SYS. SYSUSERMESSAGES table.
In Adaptive Server Anywhere,Format-stringThe length can be up to 255 bytes.
The extension value supported by The RAISERROR statement in Adaptive Server Enterprise is not supported in Adaptive Server Anywhere.
For parameters in the optional parameter list, the format string can contain placeholders. These Placeholders are in the form%Nn!, WhereNnIs an integer between 1 and 20.
The intermediate RAISERROR status and code information are lost after the process is terminated. If an error occurs when a RAISERROR is returned, the error message is returned, and The RAISERROR information is lost. The application can check the @ error global variable at different execution points to query the intermediate RAISERROR status.

Usage

The RAISERROR statement allows users to send custom errors and messages to clients.

Permission

None.

Side effects

None.

For more information, see

• Create trigger statement [T-SQL]
• On_tsql_error option [compatibility]
• Continue_after_raiserror option [compatibility]

Standards and compatibility

• SQL/92Transact-SQL extension.

• SQL/99Transact-SQL extension.

• SybaseSupported by Adaptive Server Enterprise.

Example

The following statement raises error 23000 (this error is within the user-defined error range) and sends a message to the client. Note thatError-NumberAndFormat-stringThere is a comma between parameters. The first item after the comma is interpreted as the first item in the parameter list.

RAISERROR 23000 'Invalid entry for this column: %1!', @val

In the next example, use RAISERROR to disable connection.

Create procedure DBA. login_check ()

Begin

// Allow a maximum of 3 concurrentconnections

If (db_property ('conncount')> 3) then

Raiserrors 28000

'User % 1! Is not allowed to connect -- thereare already % 2! Users logged on ',

Current user,

Cast (db_property ('conncount') as int)-1;

Else

Call sp_login_environment;

End if;

End

Go

Grant execute on DBA. login_check to PUBLIC

Go

Set optionPUBLIC. Login_procedure = 'dba. login_check'

Go