SET Xact_abort {on | OFF} set the entire transaction to terminate and roll back the method

SET Xact_abort {on | OFF}
GrammarSET Xact_abort {on | OFF} Notes

When SET Xact_abort is on, the entire transaction terminates and is rolled back if the Transact-SQL statement produces a run-time error.

When SET Xact_abort is off, sometimes only the Transact-SQL statement that produces the error is rolled back, and the transaction continues to process. If the error is serious, the entire transaction may be rolled back even if SET xact_abort is off. Off is the default setting.

Compilation errors, such as syntax errors, are not affected by SET Xact_abort.

For most OLE DB providers, including SQL Server, you must set the Xact_abort in the data modification statements in the implicit or display transaction to ON. The only thing that does not require this option is when the provider supports nested transactions.

Set XACT_ABORT settings are set at execution or runtime, not at parse time. Example

The following code example causes a foreign key conflict error to occur in a transaction that contains other Transact-SQL statements. An error occurred in the first statement set, but the other statements were executed successfully and the transaction was successfully committed. Sets the set Xact_abort to on in the second statement set. This causes the statement error to terminate the batch process and rollback the transaction. Use ADVENTUREWORKS2008R2;
Go IF object_id (n ' T2 ', n ' U ') are not NULL
DROP TABLE T2;
Go IF object_id (n ' t1 ', n ' U ') are not NULL
DROP TABLE T1;
Go
CREATE TABLE T1 (a INT not NULL PRIMARY KEY);
CREATE TABLE T2 (a INT not NULL REFERENCES T1 (a));
Go
INSERT into T1 VALUES (1);
INSERT into T1 VALUES (3);
INSERT into T1 VALUES (4);
INSERT into T1 VALUES (6);
Go
SET Xact_abort off;
Go
BEGIN TRANSACTION;
INSERT into T2 VALUES (1);
INSERT into T2 VALUES (2); --Foreign key error.
INSERT into T2 VALUES (3);
COMMIT TRANSACTION; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Go
SET Xact_abort on;
Go
BEGIN TRANSACTION;
INSERT into T2 VALUES (4);
INSERT into T2 VALUES (5); --Foreign key error.
INSERT into T2 VALUES (6);
COMMIT TRANSACTION;
Go
--SELECT shows only keys 1 and 3 added. --Key 2 insert failed and is rolled back, but-xact_abort is off and rest of transaction--succeeded. --Key 5 Insert error with Xact_abort on caused-all of the second transaction to roll back.
SELECT * from T2;
Go