mysql : declare cursor after while loop

There is (quite strange) rule in MySQL which says that DECLARE statements should go right after BEGIN before any SET, INSERT etc statements. Please refer to this article http://rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html

So, your script should look something like the following:

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;

    DECLARE exit_flag INT DEFAULT 0; ****<-- Gives error**** 
    DECLARE dday DATE;
    DECLARE dId BIGINT;
    DECLARE cur CURSOR FOR SELECT dday, id FROM tmp_date, daypart;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;

    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 < noOfdays) DO
        SET i = 1;
        INSERT INTO tmp_date(dday) VALUES (DATE_ADD(currentDate, INTERVAL dateCnt DAY));
    SET dateCnt = dateCnt + 1;
    END WHILE;

    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 ;

Hope this helps...