Mega Code Archive
What happens with stored proc transactions and exceptions
<![CDATA[
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>
]]>