Usage of RAISERROR functions in SQL Server database

The role of RAISERROR in the server database is the same as ASP. NET, like the throw new exception, is used to throw an exception or an error. This error can be captured by the program.

Common formats for RAISERROR are as follows:
RAISERROR (' Description of the error ', error severity code, wrong identity, wrong description of the parameter in the value (this can be multiple), some other parameters), the official format is described below:

1. RAISERROR ({msg_id | msg_str | @local_variable}
2. {, severity, state}
3. [, argument [,... N]])
4. [ with option [,... N]]

wherein, [, argument [,... N]] and [with option [,... N] Two items can not be written.

Explain the usage of each parameter separately:
First, {msg_id | msg_str | @local_variable}
As can be seen from this parameter, this item may be three values,
Error message number for custom error message in 1,sys.messages, custom error message can be added to sys.messages using sp_addmessage stored procedure, note that the error number of the user-defined error message should be greater than 50000.
Example: RAISERROR (50001,16,1)
2, a direct error description, example: RAISERROR (' Here is an example of an error description ', 16, 1)
3, one containing the error description variable, example:

1. DECLARE @error_mes varchar (+)
2. Set @error_mes =' Here is an example of an error description '
3. RAISERROR (@error_mes, 16, 1)

Second, severity
This parameter is the level of the user-defined error message, and we can specify a severity level between 0 and 18. Only members of the sysadmin fixed server role or users with ALTER TRACE permissions can specify a severity level between 19 and 25. To use a severity level between 19 and 25, you must select the WITH LOG option.

Note that if the error level is between 20~25, then the database considers the error to be fatal, and the database logs the error to the error log and the application log and terminates the connection to the database. Any severity level less than 0 is considered equal to 0. A severity level greater than 25 is considered equal to 25.

Third, state
This parameter can be any integer between 1~127, which can be used to identify where the error occurred, and if the same error occurs in multiple locations of a piece of code, you can set this parameter to a different value to identify the bit
Error has occurred.

Iv. [, argument [,... N]]
If the parameter {msg_id | msg_str | @local_variable} contains some substitutions, then this parameter is the specific value of the substitution, this and ASP. The String.Format usage in net is the same. Examples are as follows:

1. DECLARE @error_mes varchar (+)
2. Set @error_mes =' Here is a description of the error thrown by user%s '
3. RAISERROR (@error_mes, 16,1,' Zhang San ')

This can also be the case and may be more adaptable.

1. DECLARE @error_mes varchar (+)
2. DECLARE @error_obj varchar (+)
3. Select @error_obj =name from table_users where ...
4. Set @error_mes =' Here is a description of the error thrown by user%s '
5. RAISERROR (@error_mes, 16,1, @error_obj)

The%s in the above code means that it is substituted for a string, and if we write the example below it will be an error:

1. DECLARE @error_mes varchar (+)
2. Set @error_mes =' Here is a description of the error thrown by user%s '
3. RAISERROR (@error_mes, 16,1,1)

Because 1 is not a string, you need to use%i or%d if you want to replace it with an integer. All correspondence relate to the following:
1,%d or%i represents a signed integer
2,%u represents an unsigned integer
3,%o represents an unsigned octal number
4,%s represents a String
5,%x or%x represents an unsigned hexadecimal number

V. [WITH option [,... N]]
This parameter is the wrong custom option and can be one of the following three values:
1,log--errors are logged in the error log and application log of the Microsoft SQL Server database Engine instance. Errors logged to the error log are currently limited to a maximum of 440 bytes. Only the sysadmin fixed server role
A member or a user with ALTER TRACE permission can specify a with LOG.
2,nowait--sends the message to the client immediately.
3,seterror--set the @ @ERROR value and the Error_number value to msg_id or 50000 regardless of the severity level.

We can also use RAISERROR in the begin catch. Examples are as follows:

1. Begin try
2. RAISERROR (' This is a mistake ', 16, 1)///Note that only the severity level is between 11~19 and the control jumps to the catch block.
3. End Try
4. Begin Catch
5. declare @error_message varchar (+)
6. set @error_message =error_message ()
7. RAISERROR (@error_message, 16, 1)
8. return
9. End Catch

http://blog.csdn.net/qq_25627143/article/details/50814637

Usage of RAISERROR functions in SQL Server database