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
Answer: D
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-transactsql?view=sql-server-2017