Customizing error messages in the THROW statement

1. Customizing error messages in the THROW statement

In this lesson, we will learn how to customize error messages in the THROW statement.

2. Parameter placeholders in RAISERROR and THROW

As we learned in the previous lesson, the THROW statement does not allow parameter placeholders such as %s or %d as the RAISERROR statement does. However, there are two ways to customize our errors for the THROW statement.

3. Ways of customizing error messages

The first one is by using a variable and concatenate strings. The second one is by using the FORMATMESSAGE function.

4. Using a variable and the CONCAT function

Let's see how to use a variable to do it. In this example, we declared two variables, @staff_id, with a value of 500, and @my_message. With the use of the CONCAT function, we concatenate in the second variable, @my_message, the text 'There is no staff member for id', with the value of the variable @staff_id, and finally, with the text '. Try with another one.'. After that, the code checks if it exists any staff member whose id has the value of the variable @staff_id. If there isn't any, the THROW statement is executed, passing the variable @my_message as the message parameter. If we execute this code, as there isn't any staff member with that @staff_id, we have the following output: 'There is no staff member for id 500. Try with another one.'

5. The FORMATMESSAGE function

The second way of customizing an error message is by using the FORMATMESSAGE function. This is the syntax. It starts with the word FORMATMESSAGE followed by a message string or a message number. After that, you can optionally add arguments to replace the parameter placeholders of the first parameter.

6. FORMATMESSAGE with message string

Let's see how to use the FORMATMESSAGE function with the message string option. In this example, we assign the result of the FORMATMESSAGE function to the variable @my_message. Note that in the first parameter of the FORMATMESSAGE function, we included the parameter placeholders %d and %s. The value of the variable @staff_id will replace %d, and the text 'Try with another one.' will replace %s. After that, the code also checks if there is any staff member whose id has the value of the variable @staff_id. If there isn't any, the THROW statement is executed, passing the variable @my_message as the message parameter. If we execute this code, as there isn't any staff member with that @staff_id, we have the following output.

7. FORMATMESSAGE with message number

Now, let's see how to use the FORMATMESSAGE function with a message number. The possible message numbers are cataloged in the sys.messages view. It contains a row for each message_id or language_id of the error messages in the system. We can choose any message_id or add a new message to this view to customize our errors.

8. FORMATMESSAGE with message number

To add a new message to sys.messages, we need to do it by executing the sp_addmessage stored procedure. The first three parameters of this stored procedure, message id, severity, and message text are mandatory. The number of the message id must be greater than 50000. You can optionally specify the language. If you don't, it will be the default language of the session. In this course, we are not covering the rest of the parameters.

9. FORMATMESSAGE with message number

Let's add a new message to sys.messages by executing the stored procedure sp_addmessage. In this example, the message id is 55000; the severity, 16; the message text, 'There is no staff member for id %d. %s' and finally, the language is American English. Now, that we have the message, we can use it in the FORMATMESSAGE function, passing the number 55000 as the first parameter.

10. Let's practice!

Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.