CODEWITHSUNDEEP
mysql
Shahzaib
Shahzaib

Reputation: 127

How to call a stored procedure using while loop in mysql

My question may sound lame but still I cant get whats wrong with this code. I have made a stored procedure in sqlyog and now I want to call it. I have tried almost everything but it doesn't work.

 SET @x=1;
 WHILE @x<=10 DO
 CALL mypro();
 SET @x=@x+1;
 END WHILE;

Thanks in advance.

Upvotes: 3

Views: 9409

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

Flow control statements like IF, WHILE need to be executed in context of a function or stored procedure. If you wish to execute mypro() in a loop, that action itself must be created as a procedure. Below I will create a procedure called call_mypro_x10() which calls your original stored procedure in a WHILE loop.

DELIMITER $$
CREATE PROCEDURE call_mypro_x10()
BEGIN
  SET @x = 1;
  WHILE @x <= 10 DO
    CALL mypro();
    SET @x := @x + 1;
  END WHILE;
END$$
DELIMITER ;

Then call this procedure which wraps the other one:

CALL call_mypro_x10();

Note that the DELIMITER statements may not be necessary in all clients, and some MySQL clients supply a different method of specifying an alternate delimiter needed in stored procedure and function definitions. Apparently SQLyog supports DELIMITER

There is an open feature request to permit flow control statements as part of normal query execution.

Upvotes: 7

Related Questions