SQL Server raiseerror usage

SQL Server raiseerror usage

Raiserror
Return the User-Defined error message and set the system flag to record the error. By using the raiserror statement, the client can retrieve entries from the sysmessages table, or dynamically generate a message using the severity and status information specified by the user. After the message is defined, it is returned to the client as a server error message.

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 characters can be 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

Used to determine the spacing and alignment of user-defined error messagesCode.

Code prefix or alignment description
-(Subtract) left alignment. The result is left alignment within the specified field width.
+ (Plus) or-(minus) prefix if the output value is of the signed type, add the plus sign (+) or minus sign (-) before the output value (-).
0 (zero) zero fill if there is 0 in front of the width, add zero until the minimum width is satisfied. If 0 and-appear, 0 is ignored. If 0 is specified in integer format (I, U, X, X, O, d), 0 is ignored.
# (Number) use the 0x prefix for the X or X hexadecimal type. When the O, X, or X format is used, # Add 0, 0x, or 0x before any non-zero value. This flag is ignored when there is a # mark before D, I, or U.
''(Space) Space fill if the output value is signed and positive, then add a space before the value. This flag is ignored if it is included in the plus sign (+.

Width

An integer that defines the minimum width. The asterisk (*) allows precision to determine the width.

Precision

Is the maximum number of characters output by the output field, or the minimum number of decimal places output by the integer. The asterisk (*) allows argument to determine the precision.

{H | L} type

Used with character types D, I, O, X, X, or u to create values of the short int (h) or long int (l) type.

Character Type
D Or I signed integer
O unsigned Octal numbers
P pointer type
S string
Unsigned integer of u
X or X unsigned hexadecimal number

 

 
Float, dual-precision, and single-precision character types are not supported.

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 to the error log and application.ProgramLogs.

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.
SeterrorSet the value of @ error to msg_id or 50000, regardless of the severity level.

Note
If the sysmessages error is used and the message is created in the msg_str format shown above, the parameters (argument1, argument2, and so on) provided for the message of msg_id are provided ).

When you use raiserror to create and return user-defined error messages, use sp_addmessage to add user-defined error messages and use sp_dropmessage to delete user-defined error messages.

When an error occurs, the error code is stored in the @ error function, which stores the latest error code. For messages with a severity level of 1 to 10, @ error is set to 0 by default.

Example
A. Create a specific message
The following example shows two possible errors. The first type of error is simple. Static messages are generated. The second type of error is generated dynamically based on the attempt to modify it.

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_lv = 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 shocould be between % d and % d .',
16, 1, @ job_id, @ min_lvl, @ max_lvl)
Rollback transaction
End
B. Create a special message in sysmessages
The following example shows how to obtain the same effect as raiserror by executing the employee_insupd trigger, while raiserror uses the method for passing parameters to messages stored in the sysmessages table. The message is stored in the sp_addmessage system and added to the sysmessages table with message number 50005.

 
The following example is for example only.

Raiserror (50005, 16, 1, @ job_id, @ min_lvl, @ max_lvl)
Error message Microsoft SQL Server & s482; 2000 when a problem occurs, according to the severity level, messages in the sysmessages system table are written to the SQL Server Error Log and Microsoft Windows 2000 or Microsoft Windows NT 4.0 Application Log, or the messages are sent to the client. The SQL Server can return an error message when a problem occurs, or use the raiserror statement to manually generate an error message. The raiserror statement provides centralized error information management. Raiserror allows you to retrieve existing entries from the sysmessages table, or use hard-coded (User-Defined) messages. When raiserror returns a user-defined error message, it also sets an error in the system variable record. A message can contain a string in the C printf format, which can be filled by the parameter specified by raiserror at runtime. After the message is defined, it is sent back to the client as a server error message. Whether returned from SQL Server or through raiserror statements, each message contains a message number that uniquely identifies the error message. Indicates the Severity Level of the problem type. The error status number that identifies the source of the error (if the error can be issued from multiple locations ). Declares the message body of the problem (sometimes possible solution. For example, if the accessed table does not exist: the error message sent from select * bogus to the client is similar to the following: SERVER: error message 208, level 16, status 1 Object Name 'bogg' is invalid. Query the sysmessages table in the master database to view the SQL server error information list. For more information about sysmessages, see system error information. The severity level of the error message can be used to indicate the type of the problem encountered by Microsoft SQL Server & s482; 2000. The information with the severity level of 10 is an information message, indicating that the problem is caused by an error when the information is entered. Errors with severity levels 11 to 16 are generated by the user and can be corrected by the user. Critical errors ranging from 17 to 25 indicate software or hardware errors. When a serious error of 17 or higher occurs, notify the system administrator. The system administrator must resolve these errors and track the frequency of errors. When an error with a severity level of 17, 18, or 19 occurs, a specific statement can continue even if it cannot be executed. The system administrator should monitor all problems that can generate severity levels from 17 to 25, print error logs containing information, and find the location where the error occurred. If the problem affects the entire database, you can use DBCC checkdb (database) to determine the degree of damage. DBCC can identify the objects that must be deleted and fix the damage. If the damage range is large, the database must be restored. When a user-defined error message is specified with raiserror, the error message number greater than 50,000 and the severity level from 0 to 18 are used. Only the system administrator can issue a raiserror with severity levels ranging from 19 to 25. The error message returned when the severity level is 0 to 19 and the severity level is 10 is an information error. The error information generated by the user for severity levels from 11 to 16 can be corrected by the user. Errors with severity levels 17 and 18 are generated by resource or system errors. user sessions are not interrupted. Use sp_addmessage to add user-defined messages with severity levels from 1 to 25 to sysmessages. Only the system administrator can add messages with severity levels from 19 to 25. The system administrator should report error messages with severity levels 17 and higher. Severity Level 10: status information is an information message, indicating that the problem is caused by incorrect user input information. Severity Level 0 cannot be seen in SQL Server. Messages from severity 11 to 16 indicate that the error can be corrected by the user. Critical Level 17: insufficient resources these messages indicate that the SQL server has exhausted resources (such as database locks or disk space) or has exceeded some restrictions set by the system administrator. Severity 18: non-severe internal errors are detected. These messages indicate some type of internal software problems, but the statement execution is complete and the connection to SQL Server remains unchanged. For example, if an internal error is detected by the SQL Server Query processor during query optimization, a message with a severity of 18 is displayed. Every time a message with a severity level of 18 appears, the system administrator should be informed. Severity Level 19: An SQL server error occurs in the resource. These messages indicate that they have exceeded the internal limit of nonretriable and the current batch is terminated. Critical 19 errors rarely occur. However, once an error occurs, it must be corrected by the system administrator or primary support provider. Every time a message with a severity level of 19 appears, the system administrator should be informed. Severity levels 20 to 25 severity levels from 20 to 25 indicate system problems. This is a serious error, meaning that the process (the program code of the task specified in the completion statement) will not run. The process is frozen before it is stopped, and information about the content is recorded and then terminated. The client connection to SQL Server is closed, and the client may not be able to reconnect according to the problem. An error message with a severity level of 19 or higher will stop the current batch processing. If the error message with a severity level of 20 or higher is considered as a critical error, the customer connection will be terminated. Errors in this range may affect all processes in the database and may indicate that the database or object is corrupt. Error messages with severity levels from 19 to 25 are written into error logs. Severity Level 20: the SQL Server severe error message in the current process indicates that the statement encountered a problem. Because the problem only affects the current process, the database itself is unlikely to be damaged. Severity Level 21: the SQL Server severe errors in the database (dbid) process indicate that all processes in the current database are affected, but the database itself is unlikely to be damaged. Severity Level 22: SQL Server severe error table integrity doubt these messages indicate that the table or index specified in the message has been damaged due to software or hardware issues. Critical level 22 errors rarely occur. However, if this error occurs, run DBCC checkdb to check whether other objects in the database are damaged. The problem may only exist in the speeding cache, rather than the disk itself. If so, restarting SQL server will fix this problem. To continue working, you must connect to SQL Server again. Otherwise, use DBCC to fix the problem. In some cases, it is necessary to restore the database. If the restart is not helpful, the problem exists on the disk. Sometimes, destroying the specified object in the error message can solve this problem. For example, if the message says that SQL server finds a row with a length of 0 in a non-clustered index, delete the index and recreate it. Severity Level 23: SQL Server critical error: database integrity doubts these messages indicate that the entire database integrity is faulty due to hardware or software issues. Critical Level 23 errors rarely occur. However, if yes, run DBCC checkdb to determine the degree of damage. The problem may only exist in the speeding cache, rather than the disk itself. If so, restarting SQL server will fix this problem. To continue working, you must connect to SQL Server again. Otherwise, use DBCC to fix the problem. In some cases, it is necessary to restart the database. Severity Level 24: hardware error messages indicate that some types of media failed. The system administrator may have to reload the database. You may also need to call the hardware vendor.