Usage of RAISERROR in SQL Server (GO)

During a stored procedure, the process handles some logical errors, such as: When converting RMB to USD, it is best to throw an exception in the stored procedure when you do not have to query the desired exchange rate, so that you can find the error message ...

Its syntax is as follows:

RAISERROR({msg_id|msg_str| @local_variable }
{, severity, state}
[, argument [,... N]]
)
[With 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 1DECLARE @raiseErrorCode nvarchar( -)
SET @raiseErrorCode = CONVERT(nvarchar( -), YOURuniqueidentifier KEY)
RAISERROR('%s INVALID ID. There is no record in table', -,1, @raiseErrorCode)

--Example 2RAISERROR (
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 is message number 5.
GO

--Example 3RAISERROR(N'<<%*.*s>>', --Message text.
           Ten,           --Severity,
           1,            --State ,
           7,            --First argument used for width.
           3,            --Second argument used for precision.
N'ABCDE'); --third argument supplies the string.
--The message text returned is: << abc>>.
GO

--Example 4RAISERROR(N'<<%7.3s>>', --Message text.
           Ten,           --Severity,
           1,            --State ,
N'ABCDE'); --First argument supplies the string.
--The message text returned 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.

BEGINTRY
RAISERROR ('Error raised in TRY block.', --Message text.
                 -, --Severity.
                1 --State .
               );
ENDTRY
BEGINCATCH
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 .
               );
ENDCATCH;

--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>>';
GO

RAISERROR (50005, --Message ID.
           Ten,    --Severity,
           1,     --State ,
N'ABCDE'); --First argument supplies the string.
--The message text returned is: << abc>>.
GO

Sp_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>>';
GO

RAISERROR (50005, --Message ID.
           Ten,    --Severity,
           1,     --State ,
N'ABCDE'); --First argument supplies the string.
--The message text returned is: << abc>>.
GO

Sp_dropmessage@msgnum = 50005;
GO