{"id":1910,"date":"2019-05-01T12:58:14","date_gmt":"2019-05-01T05:58:14","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1910"},"modified":"2020-07-11T03:34:48","modified_gmt":"2020-07-10T20:34:48","slug":"sql-server-throw","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-throw\/","title":{"rendered":"SQL Server THROW"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server THROW<\/code> statement to raise an exception.<\/p>\n\n\n\n

SQL Server THROW statement overview #<\/a><\/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

THROW [ error_number ,  \n        message ,  \n        state ];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
error_number #<\/a><\/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 #<\/a><\/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 #<\/a><\/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’t specify any parameter for the THROW<\/code> statement, you must place the THROW<\/code> statement inside a CATCH<\/code> block:<\/p>\n\n\n
BEGIN<\/span> TRY\n    -- statements that may cause errors<\/span>\nEND<\/span> TRY\nBEGIN<\/span> CATCH\n    -- statement to handle errors <\/span>\n    THROW;   \nEND<\/span> CATCH\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\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 vs. RAISERROR #<\/a><\/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 <\/i>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><\/figure>\n\n\n\n
SQL Server THROW statement examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the THROW<\/code> statement to get a better understanding.<\/p>\n\n\n\n
A) Using THROW statement to raise an exception #<\/a><\/h3>\n\n\n\n
The following example uses the THROW<\/code> statement to raise an exception:<\/p>\n\n\n
THROW 50005, N'An error occurred', 1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
Msg 50005, Level 16, State 1, Line 1\nAn error occurred\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
B) Using THROW statement to rethrow an exception #<\/a><\/h3>\n\n\n\n
First, create a new table<\/a> t1<\/code> for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> t1(\n    id<\/span> int<\/span> primary key<\/span>\n);\nGO\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\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
BEGIN<\/span> TRY\n    INSERT<\/span> INTO<\/span> t1(id<\/span>) VALUES<\/span>(1<\/span>);\n    --  cause error<\/span>\n    INSERT<\/span> INTO<\/span> t1(id<\/span>) VALUES<\/span>(1<\/span>);\nEND<\/span> TRY\nBEGIN<\/span> CATCH\n    PRINT('Raise the caught error again'<\/span>);\n    THROW;\nEND<\/span> CATCH\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
(1<\/span> row affected)\n\n(0<\/span> rows affected)\nRaise the caught error again\nMsg 2627<\/span>, Level 14<\/span>, State 1<\/span>, Line 10<\/span>\nViolation of<\/span> PRIMARY KEY constraint 'PK__t1__3213E83F906A55AA'<\/span>. Cannot insert duplicate key in<\/span> object 'dbo.t1'<\/span>. The duplicate key value is (1<\/span>).<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\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 statement to rethrow an exception #<\/a><\/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
EXEC sys.sp_addmessage \n    @msgnum = 50010, \n    @severity = 16, \n    @msgtext =\n    N'The order number %s cannot be deleted because it does not exist.', \n    @lang = 'us_english';   \nGO\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement uses the message_id<\/code> 50010 and replaces the %s<\/code> placeholder by an order id ‘1001’:<\/p>\n\n\n
DECLARE<\/span> @MessageText NVARCHAR<\/span>(2048<\/span>);\nSET<\/span> @MessageText =  FORMATMESSAGE(50010<\/span>, N'1001'<\/span>);   \n\nTHROW 50010, @MessageText, 1; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
Msg 50010, Level 16, State 1, Line 8\nThe order number 1001 cannot be deleted because it does not exist.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQL Server THROW<\/code> statement to raise an exception.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t