In SQL Server, RAISERROR is used to generate a custom error message or “throw” an error back to the calling application. While the newer THROW statement is often preferred for simple error re-raising, RAISERROR remains powerful because it allows for parameter substitution (like strings or numbers) and fine-grained control over severity levels.

1. Basic Syntax

The RAISERROR statement requires three main components: the message, the severity, and the state.

RAISERROR ( { 'Message Text' | Message_ID }, Severity, State [ , argument [ ,...n ] ] )
  • Message Text: The custom error description (or an ID from sys.messages).
  • Severity: A number from 0 to 25.
  1. 0-10: Informational messages (doesn’t trigger a CATCH block).
  2. 11-16: Standard user-defined errors (triggers a CATCH block).
  3. 19-25: Fatal errors (requires WITH LOG and terminates the connection).
  • State: An integer from 0 to 255. It’s a “marker” used to identify where in the code the error happened.

2. Practical Example: Dynamic Messages

One of the biggest advantages of RAISERROR is the ability to pass variables into the message using C-style formatting (e.g., %s for strings, %d for integers).

DECLARE @Username NVARCHAR(50) = 'JohnDoe';
DECLARE @UserID INT = 101;

IF @Username = 'JohnDoe'
BEGIN
    RAISERROR ('Access Denied: User %s (ID: %d) does not have permission.', 
               16, -- Severity: Medium
               1,  -- State
               @Username, 
               @UserID);
END

3. Using RAISERROR in a TRY…CATCH

You can use RAISERROR to catch a system error, add your own context, and then pass it up to the application.

BEGIN TRY
    UPDATE Inventory SET Stock = Stock - 100 WHERE ProductID = 999;
END TRY
BEGIN CATCH
    DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrSev INT = ERROR_SEVERITY();
    DECLARE @ErrState INT = ERROR_STATE();

    -- Re-raise the error with original details
    RAISERROR (@ErrMsg, @ErrSev, @ErrState);
END CATCH;

4. Raising Errors “Ad Hoc”

If you don’t want to define a message in sys.messages beforehand, you can simply type the string. Just remember that the message string must be a literal or a variable, not a concatenated expression like 'Error' + @val.

Correct:

DECLARE @msg NVARCHAR(100) = FORMATMESSAGE('User %s not found', @UserName);
RAISERROR(@msg, 16, 1);

Categorized in:

Stored Procedures,