Developing Microsoft SQL Server Databases — Question 53

You use SQL Server to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
Either BOTH statements succeed or BOTH statements fail as a batch.
If an error occurs on the first statement, SQL should not attempt to run the second statement.
Error information should be returned to the client.
What should you do?

Answer options

• A. SET XACT_ABORT ON BEGIN TRANSACTION "¦.Statement 1 "¦.Statement 2 If @@ERROR <> 0 ROLLBACK ELSE COMMIT TRANSACTION
• B. SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION "¦.Statement 1 "¦.Statement 2 COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH
• C. SET XACT_ABORT OFF BEGIN TRY "¦.Statement 1 END TRY BEGIN TRY "¦.Statement 2 END TRY BEGIN CATCH THROW END CATCH
• D. SET XACT_ABORT OFF BEGIN TRNSACTION "¦.Statement 1 "¦.Statement 2 If @@ERROR <> 0 ROLLBACK ELSE COMMIT TRANSACTION

Correct answer: B

Explanation

The correct answer is B because it uses a TRY...CATCH block along with SET XACT_ABORT ON, ensuring that if an error occurs in Statement 1, the transaction is rolled back, and no attempt is made to execute Statement 2, while also returning error information to the client. Options A and D do not use the TRY...CATCH structure, which is necessary for error handling, and Option C does not set XACT_ABORT ON, which may lead to incomplete transaction handling.