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 TRANSACTION
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 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.
Explanation:
If an exception occurs from one of the statements in the TRY block, control is branched to the CATCH block, where the exception can be handled, logged, and so on. In this case you want to ROLLBACK the transaction if there is an error.