You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur. BEGIN TRY BEGIN TRANSACTION DELETE FROM
Person.Contact WHERE ContactID = @ContactID COMMIT TRANSACTIONEND TRYBEGIN
CATCH DECLARE @ErrorMessage nvarchar(2000) DECLARE @ErrorSeverity int DECLARE @ErrorState int SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do?
A.
Add a COMMIT TRANSACTION command to the CATCH block.
B.
Remove the COMMIT TRANSACTION command from the TRY block.
C.
Add a ROLLBACK TRANSACTION command to the CATCH block.
D.
Add a ROLLBACK TRANSACTION command to the TRY block.