Using RAISERROR

RAISERROR is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine.

RAISERROR can return either:

• A user-defined error message that has been created using the sp_addmessage system stored procedure. These are messages with a message number greater than 50000 that can be viewed in the sys.messages catalog view.

• A message string specified in the RAISERROR statement.

RAISERROR can also:

• Assign a specific error number, severity, and state.

• Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.

• Substitute argument values into the message text, much like the C language printf_s function.

Both RAISERROR and PRINT can be used to return informational or warning messages to an application. The message text returned by RAISERROR can be built using string substitution functionality similar to the printf_s function of the C standard library, whereas PRINT can only return a character string or character expression. A RAISERROR severity of 11 to 19 executed in the TRY block of a TRY…CATCH construct causes control to transfer to the associated CATCH block. Specify a severity of 10 or lower to return messages using RAISERROR without invoking a CATCH block. PRINT does not transfer control to a CATCH block.

When RAISERROR is used with the msg_id of a user-defined message in sys.messages, msg_id is returned as the SQL Server error number, or native error code. When RAISERROR is used with a msg_str instead of a msg_id, the SQL Server error number and native error number returned is 50000.

When you use RAISERROR to return a user-defined error message, use a different state number in each RAISERROR that references that error. This can help in diagnosing the errors when they are raised.

Use RAISERROR to:

• Help in troubleshooting Transact-SQL code.

• Check the values of data.

• Return messages that contain variable text.

• Cause execution to jump from a TRY block to the associated CATCH block.

• Return error information from the CATCH block to the calling batch or application.

The following example substitutes the values from the DB_ID() and DB_NAME() functions in a message sent back to the application:

DECLARE @DBID INT;
SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();

RAISERROR
    (N'The current database ID is:%d, the database name is: %s.',
    10, -- Severity.
    1, -- State.
    @DBID, -- First substitution argument.
    @DBNAME); -- Second substitution argument.
GO

This example provides the same information using a user-defined message.

EXECUTE sp_dropmessage 50005;
GO
EXECUTE sp_addmessage 50005, -- Message id number.
    10, -- Severity.
    N'The current database ID is: %d, the database name is: %s.';
GO
DECLARE @DBID INT;
SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();

RAISERROR (50005,
    10, -- Severity.
    1, -- State.
    @DBID, -- First substitution argument.
    @DBNAME); -- Second substitution argument.
GO

The following code example shows how to use RAISERROR inside a TRY block to cause execution to jump to the associated CATCH block. It also shows how to use RAISERROR to return information about the error that invoked a CATCH block.

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;