SQL Server THROW

Summary: in this tutorial, you will learn how to use the SQL Server THROW statement to raise an exception.

SQL Server THROW statement overview

The THROW statement raises an exception and transfers execution to a CATCH block of a TRY CATCH construct.

The following illustrates the syntax of the THROW statement:

THROW [ error_number ,  
        message ,  
        state ];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

error_number

The error_number is an integer that represents the exception. The error_number must be greater than 50,000 and less than or equal to 2,147,483,647.

message

The message is a string of type NVARCHAR(2048) that describes the exception.

state

The state is a TINYINT with the value between 0 and 255. The state indicates the state associated with the message.

If you don’t specify any parameter for the THROW statement, you must place the THROW statement inside a CATCH block:

BEGIN TRY
    -- statements that may cause errors
END TRY
BEGIN CATCH
    -- statement to handle errors 
    THROW;   
END CATCH
Code language: SQL (Structured Query Language) (sql)

In this case, the THROW statement raises the error that was caught by the CATCH block.

Note that the statement before the THROW statement must be terminated by a semicolon (;)

 THROW vs. RAISERROR

The following table illustrates the difference between the THROW statement and RAISERROR statement:

SQL Server THROW statement examples

Let’s take some examples of using the THROW statement to get a better understanding.

A) Using THROW statement to raise an exception

The following example uses the THROW statement to raise an exception:

THROW 50005, N'An error occurred', 1;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Msg 50005, Level 16, State 1, Line 1
An error occurred
Code language: SQL (Structured Query Language) (sql)

B) Using THROW statement to rethrow an exception

First, create a new table t1 for the demonstration:

CREATE TABLE t1(
    id int primary key
);
GO
Code language: SQL (Structured Query Language) (sql)

Then, use the THROW statement without arguments in the CATCH block to rethrow the caught error:

BEGIN TRY
    INSERT INTO t1(id) VALUES(1);
    --  cause error
    INSERT INTO t1(id) VALUES(1);
END TRY
BEGIN CATCH
    PRINT('Raise the caught error again');
    THROW;
END CATCH
Code language: SQL (Structured Query Language) (sql)

Here is the output:

(1 row affected)

(0 rows affected)
Raise the caught error again
Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK__t1__3213E83F906A55AA'. Cannot insert duplicate key in object 'dbo.t1'. The duplicate key value is (1).Code language: JavaScript (javascript)

In this example, the first INSERT statement succeeded. However, the second one failed due to the primary key constraint. Therefore, the error was caught by the CATCH block was raised again by the THROW statement.

C) Using THROW statement to rethrow an exception

Unlike the RAISERROR statement, the THROW statement does not allow you to substitute parameters in the message text. Therefore, to mimic this function, you use the FORMATMESSAGE() function.

The following statement adds a custom message to the sys.messages catalog view:

EXEC sys.sp_addmessage 
    @msgnum = 50010, 
    @severity = 16, 
    @msgtext =
    N'The order number %s cannot be deleted because it does not exist.', 
    @lang = 'us_english';   
GO
Code language: SQL (Structured Query Language) (sql)

This statement uses the message_id 50010 and replaces the %s placeholder by an order id ‘1001’:

DECLARE @MessageText NVARCHAR(2048);
SET @MessageText =  FORMATMESSAGE(50010, N'1001');   

THROW 50010, @MessageText, 1; 
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Msg 50010, Level 16, State 1, Line 8
The order number 1001 cannot be deleted because it does not exist.
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server THROW statement to raise an exception.