Question:
I’m new to Mysql Stored Procedures. Tring to return rows in a stored procedure after a LOOP. Here’s my codeBEGIN
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
[/code]
Answer:
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;
[/code]
If you have better answer, please add a comment about this, thank you!