articles >> developer >> Exception Handling in SQL Server 2000 and ...

Exception Handling in SQL Server 2000 and 2005

By : Amol Kulkarni Jul 27, 2005

Page 2 / 3



@@trancount is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless you have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.

Let’s take a look at the following code:

 

Here are the results after it is run:


(1 row(s) affected)

(1 row(s) affected)

Transaction count is :1

(1 row(s) affected)

(1 row(s) affected)

Transaction count is :2

Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with CHECK constraint 'stud_ck'. The conflict occurred in database 'AdventureWorks', table 'student', column 'marks'.
The statement has been terminated.

(1 row(s) affected)

Error is 547

Transaction count is :3
Transaction count is :2
Transaction count is :1
Transaction count is :0

From the above output you can observe that the value of @@trancount is increased with every BEGIN TRANSACTION, and decreased with every COMMIT TRANSACTION.

The last COMMIT TRANSACTION sets the value of @@trancount to 0, specifying the successful execution of the script.

As we have discussed about the different ways of error handling in SQL Server 2000, we can observe that this job is tedious in SQL Server 2000 as for every statement a local value is to be stored, which decreases the clarity in the code and increases the complexity and even the size of the code.