Administering a SQL Database Infrastructure — Question 161

You need to ensure that a stored procedure fails if an INSERT statement within the stored procedure fails.
What action should you take?

Answer options

• A. THROW 51000, 'Abort!'
• B. SET XACT_ABORT OFF
• C. SET XACT_ABORT ON
• D. TRY....CATCH

Correct answer: C

Explanation

The correct answer is C, as setting XACT_ABORT ON ensures that the transaction is aborted and any error in the INSERT will cause the stored procedure to fail. Options A and D are not sufficient on their own to handle transaction failures effectively, and B would disable the automatic rollback behavior needed to manage errors properly.