Error handling

[tapks]

Hi!

I am facing the following problem while trapping the errors.

I have a table Test (id int, no int, name varchar(20)) where id & no are the primery keyes. I want to update this table depending on the matching of id & no. I have written the following code to update the same:

update test
set name = 'James'
where id=5 and no=30

print @@error

Even if the record is not available in the given condition it is still showing the value of @@error as 0.

Pl help me in resolving the same.

Thanks in advance.

bye.

 

[Sqlsa]

Hi,
When you run an "UPDATE" , if an error occurs then only @@ERROR gets the Error Number. If the UPDATE statement does not affect any rows, it is not an error that is why @@ERROR still remains as ZERO.

But to know how many records are affected due to an UPDATE statement, you can use @@ROWCOUNT. @@ROWCOUNT gives the number of records which are affected when any SQL statement is run.


Suppose 5 rows are affected with your UPDATE statement, then @@ROWCOUNT becomes 5 and @@ERROR remains ZERO. If not a single record is affected then @@ROWCOUNT remains 0 and @@ERROR also remains ZERO.
If an error occurs then @@ERROR gets the Error number.

update test
set name = 'James'
where id=5 and no=30
------------
PRINT @@ROWCOUNT
------------
print @@error


If you want , you can store these values into variables and use them for error handling as below.

-----------------------
declare @intRecs int, @intErr int

update test
set name = 'James'
where id=5 and no=30

SELECT @intRecs=@@ROWCOUNT, @intErr=@@ERROR
------------------------

Now you have the error number and the number of records affected with the UPDATE statement. You can use these values in error handling in whichever fashion you want.


Hope this helps. If you need further help, it's all there in BOL.

Sreenivas,
avnsr@hotmail.com
-----------------

 

[tapks]

Thank you very much.