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:
| 123 | THROW [ error_number , message , state ]; |
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:
| 1234567 | BEGIN 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 (;)
THROW vs. RAISERROR
The following table illustrates the difference between the THROW statement and RAISERROR statement:
| RAISERROR | THROW |
|---|---|
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:
| 1 | THROW 50005, N’An error occurred’, 1; |
Here is the output:
| 12 | Msg 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:
| 1234 | CREATE TABLE t1( id int primary key);GO |
Then, use the THROW statement without arguments in the CATCH block to rethrow the caught error:
| 123456789 | BEGIN 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:
| 1234567 | 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 |
This statement uses the message_id 50010 and replaces the %s placeholder by an order id ‘1001’:
| 1234 | DECLARE @MessageText NVARCHAR(2048);SET @MessageText = FORMATMESSAGE(50010, N’1001′); THROW 50010, @MessageText, 1; |
Here is the output:
| 12 | Msg 50010, Level 16, State 1, Line 8The order number 1001 cannot be deleted because it does not exist. |