What happens with stored proc transactions and exceptions? : TRANSACTION « Transaction « SQL Server / T-SQL Tutorial






7> CREATE TABLE SomeData
8> (
9>     SomeColumn INT
10> )
11> GO
1>
2> --This procedure will insert one row, then throw a divide by zero exception
3> CREATE PROCEDURE NoRollback
4> AS
5> BEGIN
6>     INSERT SomeData VALUES (1)
7>
8>     INSERT SomeData VALUES (1/0)
9> END
10> GO
1>
2> --Execute the procedure
3> EXEC NoRollback
4> GO

(1 rows affected)
Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Procedure NoRollback, Line 8
Divide by zero error encountered.
1>
2> --Select the rows from the table
3> SELECT *
4> FROM SomeData
5> GO
SomeColumn
-----------
          1

(1 rows affected)
1>
2> drop PROCEDURE NoRollback;
3> GO
1>
2> drop table SomeData;
3> GO
1>








23.1.TRANSACTION
23.1.1.A transaction is bound by the ACID test. ACID stands for Atomicity, Consistency, Isolation (or Independence), and Durability:
23.1.2.Explicit Transaction Commands
23.1.3.Using Transactions
23.1.4.BEGIN TRANSACTION
23.1.5.Using Explicit Transactions
23.1.6.Forcing an exclusive table lock.
23.1.7.Transaction spread across batches:
23.1.8.Exception before transaction committing
23.1.9.statements coded as a transaction
23.1.10.A script with nested transactions
23.1.11.One batch that contains two transactions
23.1.12.What happens with stored proc transactions and exceptions?
23.1.13.Declare variable in a transaction
23.1.14.Inserting a Row into MyTable and Rolling Back the Transaction