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

View Answer

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-transact­sql?view=sql-server-2017