Querying Microsoft SQL Server 2012/2014 — Question 14

You use Microsoft SQL Server to develop a database application.
You create a stored procedure named dbo.ModifyData that can modify rows.
You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:
✑ Does not return an error
✑ Closes all opened transactions
Which Transact-SQL statement should you use?

Answer options

• A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH
• B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH
• C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH
• D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH

Correct answer: D

Explanation

The correct answer is D because it rolls back the transaction without throwing an error when an error occurs, fulfilling the requirement of not returning an error. Options A, B, and C either include error throwing or do not correctly handle the transaction count, which does not meet the specified requirements.