Which Transact-SQL statement should you include at the beginning of the stored procedure?

You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is
terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is
terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

A.
SET XACT_ABORT ON

B.
SET ARITHABORT ON

C.
TRY

D.
BEGIN

E.
SET ARITHABORT OFF

F.
SET XACT_ABORT OFF

Explanation:
http://msdn.microsoft.com/en-us/library/ms190306.aspx
http://msdn.microsoft.com/en-us/library/ms188792.aspx



Leave a Reply 5

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


Novel Purification

Novel Purification

=> When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
=> When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
So the answer is ‘A’.

Durga Prasad Palepu

Durga Prasad Palepu

A