You need to implement a stored procedure that deletes a…

HOTSPOT
Note: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text
of the scenario is exactly the same in each question in this series.
You have a database named Sales that contains the following database tables: Customer, Order, and
Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records.
Storage requirements for the Leads table must be minimized.
You need to implement a stored procedure that deletes a discontinued product from the Products table. You
identify the following requirements:
If an open order includes a discontinued product, the records for the product must not be deleted.
The stored procedure must return a custom error message if a product record cannot be deleted. The
message must identify the OrderID for the open order.
What should you do? To answer, select the appropriate Transact-SQL segments in the answer area.
Hot Area:

Answer:

Explanation:
Using TRY…CATCH in Transact-SQL
Errors in Transact-SQL code can be processed by using a TRY…CATCH construct.
TRY…CATCH can use the following error function to capture error information:
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for
any substitutable parameters such as lengths, object names, or times.
https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx