nerdexam
Microsoft

70-465 · Question #28

You are building a stored procedure for a SQL Azure database. The procedure will add multiple rows to a table. You need to design the stored procedure to meet the following requirements: - If any of…

The correct answer is D. An explicit transaction that has error handling enabled. To ensure atomicity for multi-row insertions and custom error logging with original error return, the stored procedure must use explicit transactions combined with TRY...CATCH error handling.

Submitted by omar99· Mar 5, 2026Design and implement database solutions for Azure SQL Database

Question

You are building a stored procedure for a SQL Azure database. The procedure will add multiple rows to a table. You need to design the stored procedure to meet the following requirements: - If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded. - If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure. What should you include in the design?

Options

  • AAn implicit transaction that has XACT_ABORT enabled
  • BAn explicit transaction that has XACT_ABORT disabled
  • CAn implicit transaction that has error handling enabled
  • DAn explicit transaction that has error handling enabled

How the community answered

(48 responses)
  • A
    4% (2)
  • B
    4% (2)
  • C
    10% (5)
  • D
    81% (39)

Why each option

To ensure atomicity for multi-row insertions and custom error logging with original error return, the stored procedure must use explicit transactions combined with `TRY...CATCH` error handling.

AAn implicit transaction that has XACT_ABORT enabled

Implicit transactions do not provide the explicit control necessary for robust transaction management, and `XACT_ABORT ON` prevents `CATCH` blocks from executing for many error types, thus hindering custom error auditing and reporting.

BAn explicit transaction that has XACT_ABORT disabled

While explicit transactions are correct, `XACT_ABORT OFF` requires careful manual handling within the `CATCH` block to ensure transactions are properly rolled back and to prevent further statements from attempting execution after an error.

CAn implicit transaction that has error handling enabled

Implicit transactions lack the explicit control needed to guarantee atomicity and discard all changes reliably upon an error in multi-statement operations, even with `TRY...CATCH` error handling.

DAn explicit transaction that has error handling enabledCorrect

An explicit transaction, controlled with `BEGIN TRAN`, `COMMIT TRAN`, and `ROLLBACK TRAN`, guarantees that if any part of the multi-row insertion fails, all changes are discarded, fulfilling the atomicity requirement. `TRY...CATCH` error handling allows the procedure to intercept errors, insert details into an audit table within the `CATCH` block, and then re-raise the original error using `THROW` to the caller.

Concept tested: SQL Transaction Management with TRY...CATCH Error Handling

Source: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

Topics

#explicit transactions#error handling#XACT_ABORT#stored procedures

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice