Development SQL Server Stored Procedures


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:

123THROW [ error_number ,          message ,          state ];

In this syntax:


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.


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


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:

1234567BEGIN TRY    — statements that may cause errorsEND TRYBEGIN CATCH    — statement to handle errors     THROW;   END CATCH

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 (;)


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

The message_id that you pass to RAISERROR must be defined in sys.messages view.The error_number parameter does not have to be defined in the sys.messages view.
The message parameter can contain printf formatting styles such as %s and %d.The message parameter does not accept printf style formatting. Use FORMATMESSAGE() function to substitute parameters.
The severity parameter indicates the severity of the exception.The severity of the exception is always set to 16.

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:

1THROW 50005, N’An error occurred’, 1;

Here is the output:

12Msg 50005, Level 16, State 1, Line 1An error occurred

B) Using THROW statement to rethrow an exception

First, create a new table t1 for the demonstration:

1234CREATE TABLE t1(    id int primary key);GO

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

123456789BEGIN TRY    INSERT INTO t1(id) VALUES(1);    —  cause error    INSERT INTO t1(id) VALUES(1);END TRYBEGIN CATCH    PRINT(‘Raise the caught error again’);    THROW;END CATCH

Here is the output:

123456(1 row affected) (0 rows affected)Raise the caught error againMsg 2627, Level 14, State 1, Line 10Violation of PRIMARY KEY constraint ‘PK__t1__3213E83F906A55AA’. Cannot insert duplicate key in object ‘dbo.t1’. The duplicate key value is (1).

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:

1234567EXEC 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

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

1234DECLARE @MessageText NVARCHAR(2048);SET @MessageText =  FORMATMESSAGE(50010, N’1001′);    THROW 50010, @MessageText, 1;

Here is the output:

12Msg 50010, Level 16, State 1, Line 8The order number 1001 cannot be deleted because it does not exist.

Leave a Reply

Your email address will not be published. Required fields are marked *