Menu
What is the benefit of using SET XACT_ABORT ON in a stored procedure?
533
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. 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.
SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure.
SET NOCOUNT ON is a set statement which prevents the message which shows the number of rows affected by T-SQL query statements. This is used within stored procedures and triggers to avoid showing the affected rows message.
XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn't covered by the default XACT_ABORT OFF setting.)
Since a query timeout will leave the transaction open, SET XACT_ABORT ON is recommended in all stored procedures with explicit transactions (unless you have a specific reason to do otherwise) as the consequences of an application performing work on a connection with an open transaction are disastrous.
There's a really great overview on Dan Guzman's Blog,
274
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
