1. THROW
Now that you have learned the RAISERROR statement let's discover the THROW statement.
2. THROW syntax
As we said in the previous lesson, Microsoft recommends using the THROW statement over the RAISERROR statement.
Let's see the THROW syntax. It starts with the word THROW and can optionally be followed by a list of parameters. The first parameter is the error number, the second parameter is the message, and the third parameter is the state.
Unlike the RAISERROR statement, the THROW statement allows re-throwing an original error caught by a CATCH block.
Let's see some examples.
3. THROW - without parameters
The THROW statement can be invoked without parameters within a CATCH block. In this example, we intentionally created an error, dividing the price of the orders by zero. This error makes the control pass to the CATCH block, invoking the THROW statement without parameters. The THROW statement re-throws the original error caused in the TRY block and stops the execution, so the select statement "This line is executed!", isn't executed. The re-thrown error has the same error number, severity, and state as the original one.
4. THROW - ambiguity
You have to be careful when using a statement before the THROW statement. In such a case, the statement must always end with a semicolon. Otherwise, SQL Server will have unexpected behavior.
In this example, the select statement that goes before the THROW statement doesn't end with a semicolon. SQL Server thinks that the word THROW is an alias for the select statement. So, if we execute this example, we will get the following output. We can see that the THROW statement wasn't executed.
5. THROW - ambiguity
Let's correct this script and add a semicolon after the select statement, before the THROW statement. If we execute this code, we will see the text 'This line is executed' with no column name, as it doesn't have an alias. Now, the THROW statement is executed, so we get the information about the error.
6. THROW - with parameters
The THROW statement can also be used with parameters for the error_number, message, and state. This syntax can be included within a CATCH block or outside of it.
Here is an example of the use of the THROW statement. The first parameter, 52000, is the error number. This number can be greater than or equal to 50000. The second parameter, 'This is an example', is the message. Unlike the RAISERROR statement, the THROW statement does not allow the inclusion of parameter placeholders such as %d or %s. However, we will learn how to do something similar in the next lesson. Finally, the third parameter is the state, 1 in this example.
This code generates the following output.
The THROW statement doesn't allow the specification of the severity. SQL Server always sets it to 16.
7. THROW - with parameters
You can include the THROW statement with parameters within a TRY block. In this example, as there isn't any staff whose id is 15, the THROW statement with parameters is invoked. As SQL Server always sets the severity of the THROW statement to 16, the control passes to the CATCH block, showing the error message.
8. Let's practice!
Now you will practice applying the THROW statement!