The usage of raiserror function in SQL Server database

Time:2021-1-20

The usage of raiserror function in SQL Server database

serverdatabaseThe function of raiserror in ASP.NETLike throw new exception in, it is used to throw an exception or error. This error can be caught by the program.

The common format of raiserror is as follows:
Raiserrror (‘error description ‘, error severity code, error identification, parameter values in error description (this can be multiple), and some other parameters). The official format description is as follows:


RAISERROR ( { msg_id | msg_str | @local_variable } 
 { ,severity ,state } 
 [ ,argument [ ,...n ] ] ) 
 [ WITH option [ ,...n ] ] 

Among them, [, argument [,… N]] and [with option [,… N] are optional.

Explain the usage of each parameter:

1、 {MSG}_ id | msg_ str | @local_ variable }

It can be seen from this parameter that this term may have three values,
1, sys.messages The error message number of the custom error message in_ Addmessage stored procedure added to sys.messages 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: raiserrror (‘Here is an example of error description ‘, 16,1)
3. An error description variable, for example:

declare @error_mes varchar(1000) 
set @error_ MES ='Here is an example of error description ' 
raiserror(@error_mes,16,1)

2、 Severity

This parameter is the user-defined level of the error message. 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 severity levels between 19 and 25. To use severity levels between 19 and 25, you must select the with log option.

Note that if the error level is between 20 and 25, the database will consider the error fatal, and the database will log the error to the error log and application log, and then terminate the connection to the database. Any severity level less than 0 is considered equal to 0. Severity levels greater than 25 are considered equal to 25.

3、 State

This parameter can be any integer between 1 and 127, which can be used to identify the location of the error. If the same error occurs in multiple locations of a piece of code, this parameter can be set to different values to identify the bit
There was a mistake.

4、 [, argument [,… N]]

If the parameter {MSG}_ id | msg_ str | @local_ Variable} contains some substitution characters, so this parameter is the specific value of the substitution character, which is the same as ASP.NetIn string.Format The usage is the same. Examples are as follows:

declare @error_mes varchar(1000) 
set @error_ MES ='Here is the error description caused by user% s' 
raiserror(@error_ MES, 16,1, 'Zhang San')

It can also be so. It may be more applicable.

declare @error_mes varchar(1000) 
declare @error_obj varchar(1000) 
select @error_obj=name from table_users where…… 
set @error_ MES ='Here is the error description caused by user% s' 
raiserror(@error_mes,16,1,@error_obj)

The% s in the above code means that it needs to be replaced by a string. If we write the example as follows, an error will be reported:

declare @error_mes varchar(1000) 
set @error_ MES ='Here is the error description caused by user% s' 
raiserror(@error_mes,16,1,1)

Because 1 is not a string, if you want to replace it with an integer, you need to use% I or% d. All the corresponding relations are as follows:
1% d or% I for signed integers
2% u represents an unsigned integer
3% o represents an unsigned octal number
4% s for string
5% X or% X represents an unsigned hexadecimal number

5、 [with option [,… N]]

This parameter is an incorrect custom option and can be one of the following three values:
1. Log — record errors in the error log and application log of the instance of Microsoft SQL Server database engine. Errors logged to the error log are currently limited to a maximum of 440 bytes. Only sysadmin fixed server role
Members or users with alter trace permission can specify with log.
2, nowait — send the message to the client immediately.
3, setterror — set the value of @ @ error and error_ The number value is set to MSG_ ID or 50000, regardless of severity.

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

begin try 
 Raiserror ('This is an error ', 16,1) // note that only when severity level is between 11 and 19, control will jump to the catch block. 
end try 
begin catch 
 declare @error_message varchar(1000) 
 set @error_message=error_message() 
 raiserror(@error_message,16,1) 
 return 
end catch

So far, this article about the use of raiserror function in SQL Server database is introduced in detail. For more information about the use of raiserror function in SQL server, please search previous articles of developer or continue to browse the following related articles. I hope you can support developer more in the future!