Usage of RAISERROR in SQL Server

RAISERROR is made up of Word raise error
raise to increase or improve;

The role of RAISERROR: RAISERROR is used to throw an error. [The following information is from the help of SQL Server 2005]

Its syntax is as follows:

RAISERROR ({msg_id | msg_str | @local_variable}                    {, severity, state}                    [, argument [,... N]]           )          [WI TH option [,... N]]

Briefly describe:

First parameter: {msg_id | msg_str | @local_variable}
MSG_ID: Represents a message designator that can be defined in a sys.messages table;
Use sp_addmessage to store user-defined error message numbers stored in the Sys.messages catalog view.
The error number of the user-defined error message should be greater than 50000.

MSG_STR: Indicates that it can also be a user-defined message, which can have a maximum of 2047 characters;
(if it is a constant, use n ' xxxx ', because it is nvarchar)
When you specify MSG_STR, RAISERROR throws an error message with an error number of 5000.

@local_variable: Indicates that a string variable can also be formatted in msg_str manner.

Second parameter: severity
User-defined severity level associated with the message. (This is important)
Any user can specify a severity level between 0 and 18.
[0,10] Within the closed interval, does not jump to catch;
If [11,19], then jump to catch;
If [20, Infinity], the database connection is terminated directly;

Third parameter: state
If the same user-defined error is raised in multiple locations,
Use a unique status number for each location to help locate the code snippet that caused the error.

Any integer between 1 and 127. (the default value of State is 1)
An error is generated when the state value is 0 or greater than 127!

Fourth parameter: argument
A parameter that is used in place of a variable defined in MSG_STR or in a message corresponding to msg_id.

Fifth parameter: Option
The wrong custom option, which can be any of the values in the following table:
LOG: Errors are logged in the error log and in the application log;
NOWAIT: Send the message to the client immediately;
SetError: Set the @ @ERROR value and the Error_number value to msg_id or 50000; [SQL] code example

--Example 1

DECLARE @raiseErrorCode nvarchar (= CONVERT (nvarchar), YOUR uniqueidentifier KEY) RAISERROR ('%s INVALID ID. There is no record in table',1, @raiseErrorCode)

--Example 2

RAISERROR (N'This is message%s%d.', --Message text,Ten,                        --Severity,1,                         --State , N' Number',                 --First argument. 5--Second argument. ); --The message text returned is: this isMessage number5. GO

-Example 3

RAISERROR (N'<<%*.*s>>', --Message text. Ten,           --Severity,1,            --State ,7,--first argument used forwidth. 3,--Second argument used forPrecision. N'ABCDE'); --third argument supplies thestring.--The message text returned is: << abc>>. GO

--Example 4

RAISERROR (N'<<%7.3s>>',-- Message text.            Ten,           -- Severity,           1,            --state ,           N'ABCDE  ');    string . is: <<    abc>>. GO

--Example 5

--a. Returning an error message from a CATCH block
The following code example shows how to use RAISERROR in a TRY block to make execution jump to the associated CATCH block.
It also shows how to use RAISERROR to return information about errors that call CATCH blocks.

BEGIN TRY RAISERROR ('Error raised in TRY block.', --Message text.  -, --Severity. 1--State . ); END trybegin CATCH DECLARE @ErrorMessage NVARCHAR (4000);    DECLARE @ErrorSeverity INT;    DECLARE @ErrorState INT; SELECT @ErrorMessage=error_message (), @ErrorSeverity=error_severity (), @ErrorState=error_state (); RAISERROR (@ErrorMessage,--Message text. @ErrorSeverity,--Severity. @ErrorState--State . ); END CATCH;

--Example 6

--b. Creating an ad hoc message in sys.messages
The following example shows how to raise a message stored in the Sys.messages catalog view.
The message is added to the Sys.messages catalog view with the message number 50005 through the sp_addmessage system stored procedure.

sp_addmessage @msgnum =50005, @severity=Ten, @msgtext= N'<<%7.3s>>'; Goraiserror (50005, --Message ID. Ten,    --Severity,1,     --State , N'ABCDE'); --first argument supplies thestring.--The message text returned is: << abc>>. Gosp_dropmessage @msgnum=50005; GO

--Example 7
--c. Using local variables to provide message text
The following code example shows how to use a local variable to provide the message text for a RAISERROR statement.

sp_addmessage @msgnum =50005, @severity=Ten, @msgtext= N'<<%7.3s>>'; Goraiserror (50005, --Message ID. Ten,    --Severity,1,     --State , N'ABCDE'); --first argument supplies thestring.--The message text returned is: << abc>>. Gosp_dropmessage @msgnum=50005; GO

Usage of RAISERROR in SQL Server