Exam 70-762 topic 1 question 160 discussion

Actual exam question from Microsoft's 70-762

Question #: 160
Topic #: 1

HOTSPOT -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review scree will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

To start the case study -
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements. Existing environment, and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

Background -
You have a database named Sales.
The Customer table includes a column that stores the date 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.

Tables -

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 display 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:

Show Suggested Answer

Suggested Answer: Explanation
Box 1: Try/Catch -
A TRY...CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
Box 2: ERROR_MESSAGE()
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://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

by othman_ee at Jan. 26, 2020, 8:13 a.m.

Comments

Submit Cancel

othman_ee

Highly Voted 5 years, 4 months ago

Error_Message shows you the error that was caught, but you need to create your own error including orderId so correct answer would be RaiseError.

upvoted 7 times

Nickname17

5 years, 2 months ago

Note it is RAISERROR not RaiseError

upvoted 2 times

Nelly100

5 years, 2 months ago

Same thing, as long as you dont use case sensitive collation

upvoted 2 times

Nickname17

5 years, 1 month ago

Note the issue is not about case sensitive or not but letter E in the middle.

upvoted 4 times

...

Anette

Most Recent 4 years, 11 months ago

This is similar with qusetion 2, and answer is Try/Parse and RAISERROR

upvoted 1 times

BenAsare

4 years, 10 months ago

It should be Try/Catch and NOT Try/Parse

upvoted 9 times

...

JohnFan

5 years, 4 months ago

There are two methods of throwing an error in Transact-SQL. First is using the THROW statement. THROW lets you specify an error number (50000 or greater, as 49999 and under are system reserved values); a user defined message in plain, Unicode text; and a state value which can be used to send additional information to the client. There is another command, RAISERROR, which seemingly does the same thing, with a few subtle differences. First, when specifying an error message, you can only return error number 50000 using RAISERROR. Second, you can change the error level using RAISERROR. There are a few formatting methods you can use with RAISERROR, along with a syntax form that we won’t review using custom system error messages.

upvoted 1 times

MAF63

5 years ago

Subtle Difference ? Throw will stop execution RaiseError will not

upvoted 4 times

...