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