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.
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.
References: https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx
Leave a Reply