You use Microsoft SQL Server 2012 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?

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 @@ERRCR != 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 @@ZRROR !- 0
ROLLBACK TRANSACTION;
END CATCH

Explanation:
Option A & C are wrong: IF @@ TRANCOUNT = 0, means that no transaction was executed.
@@TRANCOUNT indicates the number of BEGIN TRANSACTION encountered before the
@@TRANCOUNT code. Option B is also wrong because of the THROW clause: the requirement
says “should not return any error”.