Home » Microsoft » 70-461 v.2 » Which Transact-SQL statement should you add to the stored procedure?
A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements.
The stored procedure must use transaction management to handle errors.
You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs.
Which Transact-SQL statement should you add to the stored procedure?
A. SET ARITHABORT ON
B. SET NOEXEC ON
C. SET TRANSACTION ISOLATION LEVEL ON
D. SET XACT_ABORT ON
Correct Answer: D
Explanation/Reference:
Explanation:
SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017