Which Transact-SQL segment should you insert at line SP07?

You are performing a code review of stored procedures. Code at line SP03 fails to run (Line numbers are included for reference only.)

You need to ensure that transactions are rolled back when an error occurs.

Which Transact-SQL segment should you insert at line SP07?
A . If @@Error <> 0
B . If @@ TRANCOUNT = 0
C . If @@ TRANCOUNT > 0
D . If @@ Error = 0

View Answer

Answer: C

Explanation:

Using TRY…CATCH in a transaction The following example shows how a TRY…CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.

BEGIN TRANSACTION;

BEGIN TRY — Generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980;

END TRY BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH;

IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO

References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql