• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7727
  • Last Modified:

While Loops In MySQL

I am am MS SQL developer and I am busy trying to migrate over to MySQL. I need to populate a table for testing and I would like to do this using a while loop. I noticed that MySQL does support the while conditional statement but for some reason it does not want to work. I noticed that is I wrap it in a stored procedure then it works fine????

Please tell me if I can run a while loop directly or are all conditional statements limited to stored procedures and functions?

Set @i = 0;
while @i < 1000000 do
Insert into payment_instructions (acc_no,acc_type,br_code,br_name,bank_name,instr_val,dt_date)
Values ('4252123521','Current','632005','Witbank','ABSA',138.52,01);
Set @i = @i+1;
end while;
0
mwskuzzy
Asked:
mwskuzzy
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
MySQL indeed has the WHILE statement, this however can only be used inside a stored procedure.

hence, create a procedure:

create procedure do_job
begin
 declare i int default 0;

while i < 1000000 do
Insert into payment_instructions (acc_no,acc_type,br_code,br_name,bank_name,instr_val,dt_date)
Values ('4252123521','Current','632005','Witbank','ABSA',138.52,01);
Set i = i+1;
end while;
end


and run that (call do_job).
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now