You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend?

The correct answer is B. Set the XACT_ABORT option to on. Scenario: A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction. Currently, if one of the UPDATE statements fails, the stored procedure continues to execute. - When SET XACT_ABORT is…

Submitted by femi9· Mar 5, 2026Design and implement database solutions for SQL Server

Question

You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend?

Options

ASet the XACT_ABORT option to off
BSet the XACT_ABORT option to on.
CSet the IMPLICIT_TRANSACTIONS option to off.
DSet the IMPLICIT_TRANSACTIONS option to on.

How the community answered

(38 responses)

A
8% (3)
B
74% (28)
C
3% (1)
D
16% (6)

Explanation

Scenario: A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction. Currently, if one of the UPDATE statements fails, the stored procedure continues to execute. - When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

Topics

#T-SQL transactions#XACT_ABORT#Atomic operations

Community Discussion

No community discussion yet for this question.

Full 70-465 Practice