Showing posts with label Error handling. Show all posts
Showing posts with label Error handling. Show all posts

Oct 24, 2009

Catching row count and error number

After some days I am writing this blog.
I had a couple of training programs (total of 5.5 days) and then I need to travel to US (on official visit!) so I couldn't update the blog.
So here is the update.

If you need to track error number and row count of a statement how will you handle it?
Both of these are captured using @@ERROR and @@ROWCOUNT system variables.
But there is a catch:
They reflect the value of the immediate SQL statement irrespective of whether it is a DML statement (INSERT, UPDATE, DELETE & SELECT statements) or just a control statement. (Examples are IF, SET, WHILE) That means if you add SET @Err_Variable = @@ERROR after a DML statement, you will be able to catch the error number, but it will take the statement SET @Row_Variable = @@ROWCOUNT to return 1 always
How to resolve it?