In SQL Server, THROW is the modern, preferred way to raise an error. Introduced in SQL 2012, it is simpler than RAISERROR and follows ANSI standards. It is most commonly used inside a CATCH block to send error information back to the caller.

1. Basic Syntax

The THROW statement takes three arguments: Error Number, Message, and State.

SQL
THROW error_number, message, state;
  • Error Number: An integer between 50,000 and 2,147,483,647 (custom errors must be in this range).
  • Message: A string (up to 2,048 characters) describing the error.
  • State: An integer between 0 and 255 used to “flag” where in the code the error happened.

Example:

SQL
IF @InventoryCount < 0
    THROW 51000, 'Inventory cannot be less than zero.', 1;

2. Raising a New Error

You can use THROW to enforce business logic by manually triggering an error when a condition is met.

Example:

SQL
DECLARE @Stock INT = -10;

IF @Stock < 0
BEGIN
    -- Raising a custom error
    THROW 50005, 'Inventory levels cannot be negative.', 1;
END

3. Re-throwing an Error

The most powerful feature of THROW is its ability to re-raise an existing error. If you use THROW with no arguments inside a CATCH block, it sends the exact original error (including the original line number and error code) back to the application.

SQL
BEGIN TRY
    SELECT 1 / 0; -- Causes a Divide-by-Zero error
END TRY
BEGIN CATCH
    -- Log the error here if needed
    PRINT 'An error occurred. Re-throwing to the application...';
    
    THROW; -- No arguments needed to re-throw the original error
END CATCH;

4. The Semicolon Requirement

A common “gotcha” with THROW is that the statement immediately preceding it must be terminated with a semicolon (;). If it isn’t, SQL Server may interpret the THROW arguments as part of the previous command, leading to syntax errors.

Correct Usage:

SQL
SET @MyVar = 10; -- Semicolon is mandatory here if THROW follows
THROW 51000, 'Error message', 1;

5. Advanced Usage: Formatting Messages

Since THROW doesn’t support dynamic placeholders, you must use the FORMATMESSAGE function or string concatenation beforehand if you want to include variable data in the error message.

Example:

SQL
DECLARE @UserID INT = 99;
DECLARE @ErrMsg NVARCHAR(200) = FORMATMESSAGE('User with ID %d was not found.', @UserID);

THROW 50001, @ErrMsg, 1;

Categorized in:

Stored Procedures,