Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 2 discussion

Actual exam question from Microsoft's 70-762

Question #: 2
Topic #: 1

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:

Show Suggested Answer

Suggested Answer:

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

by alisher at Jan. 20, 2020, 6:13 a.m.

Comments

Submit Cancel

alisher

Highly Voted 5 years, 5 months ago

Should be TRY/CATCH and RAISERROR. ERROR_MESSAGE() returns the exact error that is produced in SQL Server, while RAISERROR can be customized like the questions wants.

upvoted 27 times

JohnFan

5 years, 3 months ago

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/functions/error-message-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver15

upvoted 5 times

...

Nelly100

5 years, 2 months ago

You are 100% correct!!!

upvoted 3 times

...

TheDUdeu

Most Recent 4 years, 6 months ago

Try/catch and raiseerror the question asks for custom error code which error_message does not.

upvoted 2 times

...

SoupDJ

4 years, 7 months ago

I agree - TRY/CATCH + RAISEERROR. Since the question requires a custom error message, it has to use RAISEERROR. As for the first part, there are no defined formal foreign key relationships between the tables, so deleting a discontinued product (that has open orders referencing it) won't generate an error on its own. So you can't use @@error or depend on TRY/CATCH by itself to "see" the error.

upvoted 4 times

...

kiri2020

4 years, 7 months ago

The first task - Handle errors- is not clear, are they talking about ANY errors in general? - in this case Try Catch is the correct selection, but if they talk about that manual error we have to raise the begin tran and rollback is the right answer, as it would be - IF ...open order - rase error, rollback transaction, ELSE ... delete record.

upvoted 1 times

...

Pennywiser

4 years, 10 months ago

Handle Errors:Try Catch Display:Error Message()

upvoted 1 times

...

stm22

4 years, 11 months ago

there is no command "Error Message()" Closest is "ERROR_MESSAGE": When called in a CATCH block, ERROR_MESSAGE returns the complete text of the error message that caused the CATCH block to run. The text includes the values supplied for any substitutable parameters - for example, lengths, object names, or times.

upvoted 1 times

...

Jay2

5 years ago

CREATE PROCEDURE THROWERROR AS BEGIN BEGIN TRY DECLARE @OrderId INT; SELECT @OrderId = 145 –The id of the order referencing a product that has been discontinued IF(@OrderId IS NOT NULL) BEGIN RAISERROR(‘Error in row %d’,16,1,@OrderId); END END TRY BEGIN CATCH IF(@@TRANCOUNT>0) ROLLBACK; THROW; END CATCH END GO --exec throwerror

upvoted 1 times

...

JohnFan

5 years, 3 months ago

ERROR_MESSAGE This is the text of the error message that was thrown. For example:　SELECT ERROR_MESSAGE() as ErrorMessage; DECLARE @ErrorMessage nvarchar(4000); SET @ErrorMessage = CONCAT('Error occurred during: ''',@Location,'''', ' System Error: ', ERROR_NUMBER(),':',ERROR_MESSAGE()); THROW 50000, @ErrorMessage, 1; END CATCH;

upvoted 1 times

...

paee45

5 years, 4 months ago

https://stackoverflow.com/questions/13862648/how-to-throw-a-custom-error-in-a-stored-procedure RAISERROR (@Output_Error,16,1)

upvoted 2 times

JohnFan

5 years, 3 months ago

Your result would be: Msg 50000, Level 16, State 1, Line XXXX XXXXX(According to @Output_Error) ---------------

upvoted 1 times

...