Mysql Stored Procedure – No rows fetched

June 15, 2022

I’m new to Mysql Stored Procedures.
Tring to return rows in a stored procedure after a LOOP.
Here’s my code

BEGIN
    
    DECLARE date_SD date;
    DECLARE c_stack CURSOR FOR
      select SD from t4 where date(SD) >= "2022-05-01" and date(SD)<= "2022-05-30" group by SD;
    DROP TEMPORARY TABLE IF EXISTS final_result;
    CREATE TEMPORARY TABLE final_result LIKE templaedb.temp_table;
    
    OPEN c_stack;
    read_loop: LOOP
        FETCH c_stack INTO date_SD;
        INSERT INTO final_result VALUES ('first','140','2022-05-06','','1','2','3','4','5');
        INSERT INTO final_result VALUES ('last','500','2022-05-06','','11','12','13','14','15');
    END LOOP read_loop;
    CLOSE c_stack;
    select 'Print Test';
    select * from final_result;
END

Select statement at last of the Stored Procedure is not working.

>Solution :

Try this

    DECLARE date_SD date;
            DECLARE c_stack CURSOR FOR
              select SD from t4 where date(SD) >= "2022-05-01" and date(SD)<= "2022-05-30" group by SD;
         
         /* add this*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
            DROP TEMPORARY TABLE IF EXISTS final_result;
            CREATE TEMPORARY TABLE final_result LIKE templaedb.temp_table;
            OPEN c_stack;
            read_loop: LOOP
            FETCH c_stack INTO date_SD;
/* must include*/
            IF done = 1 THEN
             LEAVE read_loop;
            END IF;
/* must include*/
                INSERT INTO final_result VALUES ('first','140','2022-05-06','','1','2','3','4','5');
                INSERT INTO final_result VALUES ('last','500','2022-05-06','','11','12','13','14','15');
            END LOOP read_loop;
            CLOSE c_stack;
            select * from final_result;