Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 166 discussion

Actual exam question from Microsoft's 70-761

Question #: 166
Topic #: 1

HOTSPOT -
You have the following Transact-SQL statement:

DELETE FROM Person -

WHERE PersonID = 5 -
You need to implement error handling.
How should you complete Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

by mlourinho at Nov. 20, 2019, 11:21 a.m.

Comments

Submit Cancel

Lukis92

Highly Voted 5 years, 6 months ago

BEGIN TRY BEGIN TRANSACTION COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH

upvoted 18 times

Anette

5 years, 2 months ago

yes, but begin transaction is before begin try in this :/

upvoted 2 times

...

Andi64

Highly Voted 5 years, 5 months ago

I think it should be: Begin Try Commit End Try Begin Catch Rollback End Catch

upvoted 11 times

...

Billybob0604

Most Recent 4 years, 6 months ago

answer is wrong. This works and should be the order, at least if you want to do it this way because more common is to start with begin try and then begin transaction if object_ID('proc_test','P') IS NOT NULL drop proc proc_test go create proc proc_test as begin tran begin try select @@trancount as trancount_try select 1/1 as valid_division --select 1/0 as invalid_division commit tran; end try begin catch select @@trancount as trancount_catch if @@trancount > 0 rollback tran; end catch go

upvoted 1 times

...

Andy7622

4 years, 7 months ago

I think something wrong with hot spot area some information is missing.

upvoted 1 times

...

sunz_1

4 years, 10 months ago

only one DDL, no need of BEGIN TRAN

upvoted 1 times

...

Backy

5 years, 1 month ago

There are 5 dropdowns with the same selections. the last dropdown is cut off. There is also the line with ... where you have to figure out the missing statement that is not part of the dropdowns, so the correct answer is // BEGIN TRANSACTION BEGIN TRY // DELETE FROM Person // WHERE PersonID = 5 COMMIT TRANSACTION END TRY ... // assumes BEGIN CATCH // IF @@TRANSCOUNT > 0 ROLLBACK TRANSACTION END CATCH // NOTE: the line ... is confusing // BEGIN TRANSACTION BEGIN TRY // DELETE FROM Person // WHERE PersonID = 5 COMMIT TRANSACTION END TRY ... // assumes BEGIN CATCH // IF @@TRANSCOUNT > 0 ROLLBACK TRANSACTION END CATCH

upvoted 6 times

...

damirbek369

5 years, 8 months ago

I think, the rest of the code is missing. And in my opiniin the right answer should be: BEGIN TRY END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH COMMIT TRANSACTION

upvoted 6 times

daniel_yes23

5 years, 3 months ago

Your commit transaction should be inside begin try end try block

upvoted 7 times

HA2020

4 years, 8 months ago

not necessarily, check out code from MS documentation where commit is after the try-catch..: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017 From it: BEGIN TRANSACTION; BEGIN TRY -- Generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO

upvoted 2 times

...

Anette

5 years, 2 months ago

this is exactly what i think too. There are some problems anyway. Begin transaction must have been inside try block and commit also but... :/. Also, come part of code are missing :(

upvoted 1 times

...

mlourinho

5 years, 8 months ago

Well, I think the right answers are: » Begin Try » Commit Transaction » End Try » RollBack Transaction

upvoted 3 times

Prides

5 years, 5 months ago

NO, you can not have any other codes between END TRY and BEGIN CATCH

upvoted 2 times

...