{"id":164,"date":"2019-09-24T08:00:36","date_gmt":"2019-09-24T08:00:36","guid":{"rendered":"http:\/\/sumitjangid.com\/?p=164"},"modified":"2019-09-24T08:03:46","modified_gmt":"2019-09-24T08:03:46","slug":"throw","status":"publish","type":"post","link":"http:\/\/sumitjangid.com\/index.php\/2019\/09\/24\/throw\/","title":{"rendered":"Throw"},"content":{"rendered":"\n

SQL Server THROW<\/code> statement overview<\/h2>\n\n\n\n

The THROW<\/code> statement raises an exception and transfers execution to a CATCH<\/code> block of a TRY CATCH<\/a><\/code> construct.<\/p>\n\n\n\n

The following illustrates the syntax of the THROW<\/code> statement:<\/p>\n\n\n\n
123<\/td>THROW [ error_number ,          message ,          state ];<\/td><\/tr><\/tbody><\/table>\n\n\n\n

In this syntax:<\/p>\n\n\n\n

error_number<\/h3>\n\n\n\n

The error_number<\/code> is an integer that represents the exception. The error_number<\/code> must be greater than 50,000<\/code> and less than or equal to 2,147,483,647<\/code>.<\/p>\n\n\n\n

message<\/h3>\n\n\n\n

The message<\/code> is a string of type NVARCHAR(2048)<\/code> that describes the exception.<\/p>\n\n\n\n

state<\/h3>\n\n\n\n

The state<\/code> is a TINYINT<\/code> with the value between 0 and 255. The state<\/code> indicates the state associated with the message.<\/p>\n\n\n\n

If you don\u2019t specify any parameter for the THROW<\/code> statement, you must place the THROW<\/code> statement inside a CATCH<\/code> block:<\/p>\n\n\n\n
1234567<\/td>BEGIN TRY    — statements that may cause errors<\/em>END TRYBEGIN CATCH    — statement to handle errors <\/em>    THROW;   END CATCH<\/td><\/tr><\/tbody><\/table>\n\n\n\n

In this case, the THROW<\/code> statement raises the error that was caught by the CATCH<\/code> block.<\/p>\n\n\n\n

Note that the statement before the THROW<\/code> statement must be terminated by a semicolon (;)<\/p>\n\n\n\n

 THROW <\/code>vs. RAISERROR<\/code><\/h2>\n\n\n\n

The following table illustrates the difference between the THROW<\/code> statement and RAISERROR<\/a><\/code> statement:<\/p>\n\n\n\n
RAISERROR<\/th>THROW<\/th><\/tr><\/thead>
The message_id<\/code> that you pass to RAISERROR<\/code> must be defined in sys.messages<\/code> view.<\/td>The error_number<\/em> parameter does not have to be defined in the sys.messages<\/code> view.<\/td><\/tr>
The message <\/em>parameter can contain printf<\/strong> formatting styles such as %s<\/code> and %d<\/code>.<\/td>The message <\/em>parameter does not accept printf<\/strong> style formatting. Use FORMATMESSAGE()<\/code> function to substitute parameters.<\/td><\/tr>
The severity<\/em> parameter indicates the severity of the exception.<\/td>The severity of the exception is always set to 16.<\/td><\/tr><\/tbody><\/table>\n\n\n\n

SQL Server THROW<\/code> statement examples<\/h2>\n\n\n\n

Let\u2019s take some examples of using the THROW<\/code> statement to get a better understanding.<\/p>\n\n\n\n

A) Using THROW<\/code> statement to raise an exception<\/h3>\n\n\n\n

The following example uses the THROW<\/code> statement to raise an exception:<\/p>\n\n\n\n
1<\/td>THROW 50005, N’An error occurred’, 1;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Here is the output:<\/p>\n\n\n\n
12<\/td>Msg 50005, Level 16, State 1, Line 1An error occurred<\/td><\/tr><\/tbody><\/table>\n\n\n\n

B) Using THROW<\/code> statement to rethrow an exception<\/h3>\n\n\n\n

First, create a new table<\/a> t1<\/code> for the demonstration:<\/p>\n\n\n\n
1234<\/td>CREATE TABLE t1(    id int primary key);GO<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Then, use the THROW<\/code> statement without arguments in the CATCH<\/code> block to rethrow the caught error:<\/p>\n\n\n\n
123456789<\/td>BEGIN TRY    INSERT INTO t1(id) VALUES(1);    —  cause error<\/em>    INSERT INTO t1(id) VALUES(1);END TRYBEGIN CATCH    PRINT(‘Raise the caught error again’);    THROW;END CATCH<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Here is the output:<\/p>\n\n\n\n
123456<\/td>(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).<\/td><\/tr><\/tbody><\/table>\n\n\n\n

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

C) Using THROW<\/code> statement to rethrow an exception<\/h3>\n\n\n\n

Unlike the RAISERROR<\/a><\/code> statement, the THROW<\/code> statement does not allow you to substitute parameters in the message text. Therefore, to mimic this function, you use the FORMATMESSAGE()<\/code> function.<\/p>\n\n\n\n

The following statement adds a custom message to the sys.messages<\/code> catalog view:<\/p>\n\n\n\n
1234567<\/td>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<\/td><\/tr><\/tbody><\/table>\n\n\n\n

This statement uses the message_id<\/code> 50010 and replaces the %s<\/code> placeholder by an order id \u20181001\u2019:<\/p>\n\n\n\n
1234<\/td>DECLARE @MessageText NVARCHAR(2048);SET @MessageText =  FORMATMESSAGE(50010, N’1001′);    THROW 50010, @MessageText, 1; <\/td><\/tr><\/tbody><\/table>\n\n\n\n

Here is the output:<\/p>\n\n\n\n
12<\/td>Msg 50010, Level 16, State 1, Line 8The order number 1001 cannot be deleted because it does not exist.<\/td><\/tr><\/tbody><\/table>\n","protected":false},"excerpt":{"rendered":"

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… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,18,19],"tags":[],"_links":{"self":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/164"}],"collection":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/comments?post=164"}],"version-history":[{"count":1,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/164\/revisions"}],"predecessor-version":[{"id":165,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/164\/revisions\/165"}],"wp:attachment":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/media?parent=164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/categories?post=164"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/tags?post=164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}