0

I'm trying to create a procedure with a loop inside, but when i call it just show the first record and dont interate.

delimiter //
create procedure load_foo_test_data()
begin

declare v_max int unsigned default 10;
declare v_counter int unsigned default 1;

select COUNT(*) into v_max FROM emp;

start transaction;
while v_counter < v_max do
    select * from emp where emp_id = v_counter;
    set v_counter=v_counter+1;
end while;
commit;
end 
//
delimiter ;

when i call the procedure, it shows me just the first record (with ID 1).

I tried with "select v_counter" and only shows 1 too.

and tried with loop instead while and same thing.

i'm working with sequel pro in mysql 5.5

Thanks!

Vertig0
  • 623
  • 3
  • 15
  • Why is the loop surrounded by a 'start transaction/commit'? Not related to your error (probably not) but look v odd. – ethrbunny Dec 13 '12 at 14:29
  • The truth is, i was trying only with loop (not while and not transaction/commit) and i had the same problem. Looking for a solution i found this example (the original has and "insert" not "select"), but dont fix the problem of first record only. – Vertig0 Dec 13 '12 at 14:33
  • If you manually do multiple selects, can you program handle all the resultsets? Why do you want to do multiple selects instead of just one? – Erik Ekman Dec 13 '12 at 15:01
  • I dont want that, i'm using it to try the loop, but loop dont iterate :/ – Vertig0 Dec 13 '12 at 15:41
  • What happens if you force v_max to some value? Then just put 'select 1' inside it. – ethrbunny Dec 13 '12 at 17:00

2 Answers2

-1

I think you should use 

while ( ) {

    // do this

}
Richie
  • 1,398
  • 1
  • 19
  • 36
-1

Try to use 

SET v_counter = 0;    
REPEAT
IF v_counter < v_max do THEN
    select * from emp where emp_id = v_counter;
    set v_counter=v_counter+1;
END IF;
UNTIL v_counter END REPEAT;
melic
  • 266
  • 2
  • 15