[FIXED] Use cursor within stored procedure

Issue

I'm trying to make a stored procedure that include a cursor inside it and fill one of my tables based on another table's data , every day .

I think I'm doing something wrong with syntax , I already wrote a simple Stored procedure with cursor and it worked totally right , but when it get a little more complicated it does not work any more . I'm getting

Error Code: 1064. You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'DECLARE brandId int ;' at line 1.

Please note that I'm using Mysql 5.7 and I'm creating this at phpmMyAdmin .

CREATE PROCEDURE ّFillCommentGrowth()
 BEGIN
 DECLARE brandId int;
 DECLARE todayComment int ; 
 DECLARE brandCount int ;
 DECLARE yesterdayComment int; 
 DECLARE crs CURSOR for SELECT id from brands;
 SET brandCount = (SELECT count(*) from brands);
 open crs;
 WHILE brandCount > 0 DO
 FETCH crs into brandId ;
set todayComment = (select IFNULL((select count(*) from comments as c where date(c.created_at)  =  date(subdate(NOW(),1)) and c.brand_id = brandId ),0));
set yesterdayComment = (select IFNULL((select commentAmount from commentsGrowth where moment = date(subdate(NOW(),2)) and brand_Ref= brandId),0)); 
INSERT INTO commentsGrowth
( 
brand_Ref, 
commentAmount, 
diffrenceByYesterday, 
degree, 
AmountPercent, 
moment) 
VALUES 
(brandId , 
todayComment, 
(todayComment - yesterdayComment ) , 
(((ATAN(todayComment - yesterdayComment )*180))/PI()), 
(degree*(1.1)), 
date(subdate(NOW(),1))); 
 SET  brandCount = brandCount - 1; 
 END WHILE;
 close crs;
 END 

Solution

The error you are getting has nothing to do with cursor. You need to change the DELIMITER from standard semicolon (;). For example

DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
 DELIMITER ;

The DELIMITER statement changes the standard delimiter which is semicolon ( ; ) to another. In this case, the delimiter is changed from the semicolon( ; ) to double-slashes //. Why do we have to change the delimiter? Because we want to pass the stored procedure to the server as a whole rather than letting mysql tool interpret each statement at a time. Following the END keyword, we use the delimiter // to indicate the end of the stored procedure. The last command ( DELIMITER; ) changes the delimiter back to the semicolon (;).

Answered By - Taimur Khan