Exception Handling in SQL Server 2000 and 2005



@@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.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |