SQL Server/T-SQL Tutorial/System Settings/XACT ABORT

Modifying exception types with XACT_ABORT

4> SET XACT_ABORT ON
5> SELECT POWER(2, 32)
6> PRINT "This will NOT print!"
7> GO
Msg 232, Level 16, State 3, Server J\SQLEXPRESS, Line 5
Arithmetic overflow error for type int, value = 4294967296.000000.
1>
2>

XACT_ABORT and transactions

4> SET XACT_ABORT ON
5> BEGIN TRANSACTION
6> GO
1> SELECT 1/0 AS DivideByZero
2> GO
Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 1
Divide by zero error encountered.
1> SELECT @@TRANCOUNT AS ActiveTransactionCount
2> GO
ActiveTransactionCount
----------------------
                     0
(1 rows affected)
1>
2>
3>

XACT_ABORT in a stored procedure

6> CREATE PROCEDURE NoRollback
7> AS
8> BEGIN
9>     SET XACT_ABORT ON
10>
11>     BEGIN TRANSACTION
12>         INSERT SomeData VALUES (1)
13>
14>         INSERT SomeData VALUES (1/0)
15>     COMMIT TRANSACTION
16> END
17> GO
1>