Exam 70-761 topic 1 question 188 discussion

Actual exam question from Microsoft's 70-761

Question #: 188
Topic #: 1

You are performing a code review of stored procedures. Code at line SP03 fails to run (Line numbers are included for reference only.)

You need to ensure that transactions are rolled back when an error occurs.
Which Transact-SQL segment should you insert at line SP07?

A. If @@Error <> 0
B. If @@ TRANCOUNT = 0
C. If @@ TRANCOUNT > 0
D. If @@ Error = 0

Show Suggested Answer

Suggested Answer: C 🗳️
Using TRY...CATCH in a transaction
The following example shows how a TRY...CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.
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 -
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

by lh2607 at Dec. 15, 2020, 7:41 p.m.

Comments

Submit Cancel

Andy7622

4 years, 4 months ago

no comments

upvoted 2 times

...

lh2607

4 years, 4 months ago

Correctimundo

upvoted 3 times

...

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 188 discussion

Comments

Andy7622

lh2607

SY0-701