RAISERROR

SQL Server

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

• msg_id | msg_str | @local_variable: This parameter specifies the error message to be returned. Either a specific error number (msg_id), a string of text (msg_str), or the value of a local variable (@local_variable) that contains the message, can be provided.
• severity: This parameter represents the user-defined severity level of the error. The server will react to this numeric value in different ways, depending upon its severity. Severity levels greater than 18 can only be specified by members of the sysadmin and processadmin fixed server roles.
• state: This parameter indicates the state number, which is an integer value between 1 and 127. Different states can be used to distinguish the different areas of your code where the same error may occur.
• argument [ ,…n ]: This parameter is optional and represents a comma-separated list of arguments to replace format specifiers in the message string or stored procedure.
• with option [ ,…n ]: This is an optional parameter that allows you to specify additional information about the error, such as whether it is logged in Windows Application log (WITH LOG) or whether the rule that caused it to raise is terminating (WITH NOWAIT).

Example

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- This will print an error message.
    RAISERROR ('Error occurred: %s', 16, 1, ERROR_MESSAGE());
END CATCH

Output

Msg 50000, Level 16, State 1, Line 7
Error occurred: Divide by zero error encountered.

Explanation

The RAISERROR function is used to generate an error message and initiate error processing for the session. The example tries to divide by zero, which generates a math error. The CATCH block then captures that error and uses RAISERROR to present it in a more user-friendly format. The arguments of RAISERROR function are the error message text, severity level, state, and an argument that’s inserted in the ‘%s’ placeholder in the message string. The ERROR_MESSAGE() function retrieves the text of the error message that caused the CATCH block to be run.