title: mysql : declare cursor after while loop
body: <p>I have created one stored procedure. In that after performing some operation I want to declare cursor and get data from that cursor. But, it gives syntax error. Below is my stored procedure</p>
  
  <pre><code>DELIMITER $$
  
  USE `adserver`$$
  
  DROP PROCEDURE IF EXISTS `getDaypartTimeDetail`$$
  
  CREATE DEFINER=`root`@`localhost` PROCEDURE `getDaypartTimeDetail`(currentDate DATE,noOfdays INT,cityId BIGINT)
  BEGIN
      DECLARE TotalFiles, TotalDuration BIGINT;
      DECLARE i INT;
      DECLARE da DATE;
      DECLARE dateCnt INT;
  
  
      SET dateCnt = 0;
      SET TotalFiles = 0;
      SET TotalDuration = 0;
      DROP TEMPORARY TABLE IF EXISTS tmp_date;
      DROP TEMPORARY TABLE IF EXISTS temp_data;
  
      CREATE TEMPORARY TABLE tmp_date(dday DATE); 
      CREATE TEMPORARY TABLE temp_data(dday DATE,daypartId INT,totalFile BIGINT,totalDur BIGINT); 
  
      WHILE(dateCnt &lt; noOfdays) DO
          SET i = 1;
          INSERT INTO tmp_date(dday) VALUES (DATE_ADD(currentDate, INTERVAL dateCnt DAY));
      SET dateCnt = dateCnt + 1;
      END WHILE;
  
      DECLARE exit_flag INT DEFAULT 0; ****&lt;-- Gives error**** 
      DECLARE dday DATE;
      DECLARE dId BIGINT;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;
      DECLARE cur CURSOR FOR SELECT dday, id FROM tmp_date, daypart;
  
      OPEN cur;
          all_details_id:LOOP
              FETCH cur INTO dday,dId;
              IF record_not_found THEN
                  LEAVE all_details_id;
              END IF;
          INSERT INTO temp_data(dday,daypartId) VALUES (dday,dId);
          END LOOP all_details_id;
      CLOSE cur;
  
      SELECT * FROM temp_data;
  
  END$$
  
  DELIMITER ;
  </code></pre>