1. RAISERROR
Congratulations on completing Chapter 1 on Error Handling.
In this Chapter, you will learn how to raise errors, re-throw original errors, and create your own defined errors.
2. Raise errors statements
SQL Server provides two statements to raise errors. The first one is the RAISERROR statement, which will be studied in this lesson. The second one is the THROW statement, which will be explained in the next lesson.
Microsoft suggests using the THROW statement in the new applications, but it is still common to see the RAISERROR statement in our current applications.
3. RAISERROR syntax
This is the RAISERROR syntax. It starts with the word RAISERROR. Notice it only has one E, not two.
The first parameter can be a message string, a message id, or a variable that contains the message string. The second parameter is the severity, and the third parameter is the state. After these parameters, you can optionally add arguments, like strings or numbers. If the message string has some parameter placeholders such as %s or %d, these arguments will replace them. We will explain it in detail in the next slides. Finally, you can specify additional options. These options won't be covered in this course, so for further information, you can have a look at Microsoft documentation.
4. RAISERROR with message string
Let's see an example of the RAISERROR statement with message string.
In the first line, we check if there is any staff member whose id is 15. If there isn't, we invoke RAISERROR with three parameters. The first parameter is the message text 'No staff member with such id'. The second one, 16, is the severity level. Finally, the last one is the state.
If we execute this example, as there is no staff member whose id is 15, we will get the following output.
If we don't specify an error number, the error number will always be 50000.
5. RAISERROR with message string
Let's change the message text included on the previous RAISERROR statement and add some parameter placeholders. Instead of 'No staff member with such id', let's change the words 'staff member' by the % symbol, followed by the letter s. Let's also include %d. %s is used for string values, and it will be replaced by the penultimate parameter of this function, 'staff member'. %d is used for integer values, and it will be replaced by the last parameter of this function, 15.
If we execute this example, we will get this output.
If you want to include the % character in your message, you will have to write it twice.
Apart from the letters s and d, the % symbol can be followed by other characters for other purposes. You can deepen your knowledge by looking at Microsoft documentation.
6. RAISERROR with error number
In the RAISERROR statement, we can also specify an error number instead of a message string. This error number comes from sys.messages.
We will learn more about the use of sys.messages in the lesson dedicated to the FORMATMESSAGE function.
7. RAISERROR - Example with TRY...CATCH
Let's include the RAISERROR statement inside a TRY...CATCH construct to see what happens.
In this example, the severity level of the RAISERROR statement is 9. If you remember from the previous chapter, severity levels lower than 11 are not catchable by the CATCH block. So here, this error won't be caught by the CATCH block. This will be the output.
8. RAISERROR - Example with TRY...CATCH
However, if we set up the severity level between 11 and 19, the error will be caught by the CATCH block. In this example, the severity level is 16, so the output will be "You are in the CATCH block".
9. Let's practice!
Now you will practice applying the RAISERROR statement!