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.
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.
WHILEneeds to be executed in that context.