SQL Server quiz

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 TRAN
 DELETE FROM Person.Contact 
 WHERE ContactID = @ContactID 
 COMMIT TRAN
END TRY
BEGIN 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 TRAN command to the CATCH block.
B. Remove the COMMIT TRAN command from the TRY block.
C. Add a ROLLBACK TRAN command to the CATCH block.
D. Add a ROLLBACK TRAN command to the TRY block.

Answer:[C]
Highlight to find out the answer.