mysql - SQL cursor & stored procedure order arrangement -

i working on stored procedure update order field in product table. works problem the last item in loop(cur), increased twice instead of once (so dubbeled). so:

+-----------------+ |product + order  | |_id     |        | |        |        | |  1     |   0    | |  2     |   1    | |  etc.. |   etc..| |  36    |   35   | |  37    |   36   | |  38    |   38   | |                 | +-----------------+

i cant figure out why. link table(categoryproduct) in case goes 38 category_id of 2 call curorder(2);

stored procedure:

delimiter // create procedure curorder(     in catid int ) begin     declare done int default false;     declare int default 0;     declare p int;     declare cur cursor select product_id test.categoryproduct category_id = catid;     declare continue handler not found set done = true;     open cur;         read_loop: loop             fetch cur p;             update  `test`.`product` set  `order` =   `product`.`product_id` =p;             set = + 1;             if done                 leave read_loop;             end if;         end loop;     close cur; end // delimiter ; 

the database mysql database. suggestions improving procedure welcome.

thanks in advance.

edit:

i tried place set statement beneath if statement no result.

you should put:

if done   leave read_loop; end if; 

above update statement, last time mysql walks trough loop uses old variables because there no new 'p'. incremented.

i way debug stored procedures log table:

create table procedurelog (   id integer auto_increment,   description text,   primary key (id) ); 

for case can log update parameters follow query:

insert  `test`.`procedurelog` (`id` ,`description`) values (null,  concat('id: ', cast(p char), ' order: ', cast(i char))); 

good luck!