Use of raiserror in SQL Server2000 Stored Procedure

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