Hello, I have a problem with Cursors.

My Proc looks like this:

CREATE PROCEDURE UpdateState()
BEGIN
  DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
  DECLARE State CHAR(2) DEFAULT '';

  PREPARE Stmt FROM 'SELECT * FROM Customer WHERE State=''FL''';

  OPEN CustCursor;

  FETCH FIRST FROM CustCursor (State) INTO State;

  WHILE NOT EOF(CustCursor) DO
     UPDATE CustCursor SET State='NY';
     FETCH NEXT FROM CustCursor (State) INTO State;
  END WHILE;
END

Only the half of selected records are updated. I think because the field "State" is in the clause WHERE. I don't want to use somethink like "if...
end if".

How can I walk around ?

Thank's in advance.
Paul.

Paul,

> Only the half of selected records are updated. I think because the field
> "State" is in the clause WHERE.

Updating the current record to change the state value will make it no longer
appear in the results list which then places the cursor on the next record.
When you call the FETCH NEXT, it moves it forward again.  So you are
basically updating every other record.

Two suggestions.  The first is to replace the FETCH NEXT with a FETCH FIRST
which should always put you on the first record.

Secondly, if you are updating all of the customers that are in FL to NY, why
not just run a simple update statement like:

 update cust set state = 'NY where state = 'FL'

instead of having to deal with walking the cursor.

--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 Atrex Inventory Control/POS -
    Big business features without spending big business bucks!

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Paul,

as Terry indicated the reason for your problem is that you declared a
sensitive cursor.

Generally you can use an insensitive cursor to iterate the resulting
dataset without changing it.

Of course then you cannot update the cursor. Instead you have you use an
update statement like

 Execute Immediate 'update MyTable set State = ? where ID = ? and state
= ''NY''' using MyState,MyId ;

Anyway solution #2 by Terry should do the trick.

Regards Uli

Thank you Terry, Thank you Uli for your Help.
I don't want to update all records. My real proc is a big proc and i give you just a sample.
Finally i found the error. Just use "FETCH RELATIVE 0" instead of "FETCH NEXT" and this when updating field used in
the clause "WHERE". It's same when deleting Records inside Cursors.
Regards Paul