Raiserror (TRANSACT-SQL)

I found it in detail on msdn.

Generate error messages and start session error handling. Raiserror can reference user-defined messages stored in the SYS. Messages directory view, or dynamically create messages. This message is returned to the calling application as a server error message, or to try... The associated Catch Block constructed by catch.
Syntax

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

Parameters

Msg_id

Use sp_addmessage to store the User-Defined error message number in the SYS. Messages directory view. The error code of the custom error message must be greater than 50000. If msg_id is not specified, raiserror triggers an error message with error 50000.

Msg_str

The user defines the message. The format is similar to that of the printf function in the C standard library. The error message can contain a maximum of 2,047 characters. If the number of characters in a message is equal to or greater than 2,048, only the first 2,044 characters can be displayed and a ellipsis is added to indicate that the message has been truncated. Note that, due to the internal storage behavior, the number of characters used in place of the parameter is more than the number of characters displayed in the output. For example, replace parameter % d with a value of 2 to generate a character in the message string, but it also occupies three other storage strings internally. This storage requires that the number of characters that can be used for message output be reduced.

When msg_str is specified, raiserror will trigger an error message with the error code 5000.

Msg_str is a string with an optional embedded conversion specification. Each Conversion Type defines how the values in the parameter list are formatted and placed in the field at the conversion type position in msg_str. The conversion format is as follows:

% [[Flag] [width] [. Precision] [{H | L}] Type

Parameters that can be used in msg_str include:

Flag

Code used to determine the spacing and alignment of replaced values.

Code	Prefix or alignment	Description
-(Minus sign)	Left aligned	Left-aligned parameter value within the given field width.
+ (Plus sign)	Symbol prefix	If the parameter value is of the signed type, add the plus sign (+) or minus sign (-) before the parameter value (-).
0 (0)	Zero Filling	Add zero before the output before reaching the minimum width. If 0 and minus sign (-) appear, 0 is ignored.
# (Number)	Use the 0x prefix for the hexadecimal type of X or X	When the O, X, or X format is used, the numeric sign (#) adds 0, 0x, or 0x before any non-zero value. This sign is ignored when the sign D, I, or U is preceded by a number sign.
''(Space)	Space Filling	If the output value is signed and positive, a space is added before the value. This flag is ignored if it is included in the plus sign (+.

Width

An integer that defines the minimum width of the field where the parameter value is placed. If the length of the parameter value is equal to or greater than width, print the value without filling. If the value is smaller than width, the value is filled with the specified length in width.

Asterisk (*) indicates that the width is specified by related parameters in the parameter list. The width must be an integer.

Precision

The maximum number of characters obtained from the string value parameter value. For example, if a string has five characters and the precision is 3, only the first three characters of the string value are used.

For integer values, precision refers to the minimum number of digits to print.

Asterisk (*) indicates that the precision is specified by the relevant parameters in the parameter list. The precision must be an integer.

{H | L} type

Used with character types D, I, O, X, X, or u to create a struct int (h) value or longint (l) value.

Type specification	Indicates
D Or I	Signed integer
O	Unsigned octal number
S	String
U	Unsigned integer
X or X	Unsigned hexadecimal number

Note:

These type specifications are based on the specifications originally defined for the printf function in the C standard library. The type specification used in The raiserror message string is mapped to the transact-SQL data type, while the specification used in printf is mapped to the C language data type. When a Transact-SQL statement does not have a data type similar to the associated C data type, raiserror does not support the type specification used in printf. For example, raiserror does not support the % P specification for pointers, because Transact-SQL does not have a pointer data type.

Note:
To convert a value to the transact-SQL bigint data type, specify % i64d.

@ Local_variable

Is a variable that can be of any valid character data type. The format of the contained string is the same as that of msg_str. @ Local_variable must be char or varchar, or can be implicitly converted to these data types.

Severity

The severity level of the message associated with the user-defined message. When msg_id is used to trigger a user-defined message created using sp_addmessage, the severity specified on raiserror overwrites the severity specified in sp_addmessage.

Any user can specify a severity level between 0 and 18. Only SysAdmin fixed server role members or users with alter trace permissions can specify the severity level between 19 and 25. To use a severity level between 19 and 25, You must select the with log option.

Note:
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 Log.

Note:
Severity levels smaller than 0 are interpreted as severity levels 0. The severity level greater than 25 is interpreted as 25.

State

Any integer between 1 and 127. The negative value of state is 1 by default. If the value is 0 or greater than 127, an error is generated.

If the same user-defined error is thrown at multiple locations, you can use a unique status code for each location to locate the code segment that causes the error.

Argument

This parameter is used to replace msg_str or the variable parameter defined in the message corresponding to msg_id. There can be 0 or more replicas, but the total number of Replicas cannot exceed 20. Each substitution parameter can be a local variable or has any of the following data types: tinyint, smallint, Int, Char, varchar, nchar, nvarchar, binary, or varbinary. Other data types are not supported.

Option

Incorrect custom options can be any value in the following table.

Value	Description
Log	Log errors in Microsoft SQL Server database engine instance error logs and application logs. Currently, errors recorded in error logs are limited to a maximum of 440 bytes. Only SysAdmin fixed server role members or users with alter trace permissions can specify with log.
Nowait	Send the message to the client immediately.
Seterror	Set the values of @ error and error_number to msg_id or 50000, so you do not need to consider the severity level.

Remarks

Errors generated by raiserror are the same as errors generated by database engine code. The value specified by raiserror is reported by system functions such as error_line, error_message, error_number, error_procedure, error_severity, error_state, and @ error. When a raiserror runs in a try block when the severity level is 11 or higher, it transfers the control to the associated catch block. If raiserror is run in the following circumstances, the error is returned to the caller:

• Run outside the scope of any try block.
  
• Run in try block when the severity level is 10 or lower.
  
• Terminate a database connection when the severity level is 20 or higher.
  

The catch block can be re-triggered by raiserror by using system functions such as error_number and error_message to retrieve the original error message. For messages with a severity level of 1 to 10, the default value of @ error is 0. For more information, see use try... catch in transact-SQL.

When msg_id specifies the user-defined messages available in the SYS. Messages directory view, raiserror processes messages in the text column according to the same rule applied to the user-defined message text specified by msg_str. User-defined message text can contain conversion specifications, and raiserror maps parameter values to conversion specifications. Use sp_addmessage to add user-defined error messages, and use sp_dropmessage to delete user-defined error messages.

Raiserror can return messages to the calling application instead of print. Raiserror supports replacement of characters similar to the printf function in the C standard library, but not the print Statement of transact-SQL. The print statement is not affected by the try block. When the severity is from 11 to 19, The raiserror that runs in the try block transfers the control to the associated catch block. Specify the severity level as 10 or lower to use raiserror to return messages in the try block without calling the Catch Block.

In general, continuous parameters replace Continuous Conversion specifications. The first parameter replaces the first conversion specification, the second parameter replaces the second conversion specification, and so on. For exampleRaiserrorStatement, the first parameterN 'number'Replace the first conversion Specification% S, Second parameter5Replace the second conversion Specification% D.

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

If an asterisk (*) is specified for the width or precision of the conversion type, the value to be used for width or precision is specified as an integer. In this case, a conversion type can use up to three parameters for width, accuracy, and replacement.

For exampleRaiserrorThe statement returns the same string. The width and Precision values in one specified parameter list, and the other specify the width and Precision values in the conversion specification.

RAISERROR (N'<<%*.*s>>', -- Message text.
           10, -- 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
RAISERROR (N'<<%7.3s>>', -- Message text.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO

Example

A. return an error message from the Catch Block

The following code example shows howTryBlock Used inRaiserrorSkip the execution to the associatedCatchBlock. It also shows how to useRaiserrorResponseCatchBlock Error information.

Note:
Raiserror only generates errors in the statuses of 1 to 127. Because the database engine may cause an error in the status of 0, we recommend that you check the error status before passing the error status returned by error_state To The raiserror status parameter.

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

B. Create an ad hoc message in SYS. Messages

The following example shows how to trigger messages stored in the SYS. Messages directory view. The message passesSp_addmessageSystem stored procedures, with the message number50005Add to SYS. Messages directory view.

sp_addmessage @msgnum = 50005,
              @severity = 10,
              @msgtext = N'<<%7.3s>>';
GO
RAISERROR (50005, -- Message id.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO
sp_dropmessage @msgnum = 50005;
GO

C. Use local variables to provide message text

The following code example shows how to use a local variableRaiserrorThe statement provides message text.

DECLARE @StringVariable NVARCHAR(50);
SET @StringVariable = N'<<%7.3s>>';

RAISERROR (@StringVariable, -- Message text.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO