Which Transact-SQL statement should you use?

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?

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



Leave a Reply 1

Your email address will not be published. Required fields are marked *


Peter

Peter

‘a’ – if trancount is 0, so what you’re planning to rollback? wrong;
‘b’ – logically is ok, but because of ‘throw’ it would throw error out of this procedure, and text says: “does not return an error”;
‘c’ – same as ‘a’ and ‘b’;
‘d’ – correct.