Concatenating the message
You need to prepare a script to select all the information about the members from the staff table using a given first_name.
If the select statement doesn't find any member, you want to throw an error using the THROW statement. You need to warn there is no staff member with such a name.
This exercise is part of the course
Transactions and Error Handling in SQL Server
Exercise instructions
- Assign to
@my_messagethe concatenation of 'There is no staff member with ', with the value of@first_nameand with ' as the first name.'. - Use
THROWwith 50000 as the error number,@my_messageas the message parameter, and 1 as the state. - Replace the name 'Pedro' in the
DECLAREstatement at the beginning with a name that doesn't exist (e.g. 'David') and click Run Code (not Run Solution). You will see the error. - Change again the name to 'Pedro' and run the code without errors.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
DECLARE @first_name NVARCHAR(20) = 'Pedro';
-- Concat the message
DECLARE @my_message NVARCHAR(500) =
___('There is no staff member with ', ___, ' as the first name.');
IF NOT EXISTS (SELECT * FROM staff WHERE first_name = @first_name)
-- Throw the error
___ ___, ___, ___;