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 @@ERROR != 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 @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH
D
D still is not correct, though.
CATCH block is executed ONLY when there is error, therefore IF @@ERROR != 0 is pointless IMO. it should be IF @@TRANCOUNT > 0, which means there are still open transaction.
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR>0
ROLLBACK TRANSACTION;
END CATCH
Sorry, I mean IF @@TRANCOUNT > 0
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK TRANSACTION;
END CATCH
D