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. |