MYSQL: calling a stored procedure inside another
I am aware that this has been asked in a plethora of variations on this site, but i have become thoroughly stuck in implementing my own variation. I think it has something to do with my understanding of the
DECLARE statement and
@variables in stored procedures.
In short, I am trying to create a function to populate a stats table using a function I have already written to sum up a member's attendance, to be able to not recalculate this every time an overview is asked for... ( I am not opposed to just including it in this procedure, but I would like to be able to call it separately as well, and i like the idea of code reuse.
My initialization of the stats table:
drop table if exists core_stat_totals; create table core_stat_totals (id int unsigned, half_year char(7), num_attendance int default 0); truncate core_stat_totals; insert into core_stat_totals select id, '', 0 from core_medlemmer ; select * from core_stat_totals;
and my procedure:
drop procedure if exists updatestats; delimiter // create procedure updatestats() begin DECLARE yr int default 2008; DECLARE dt int default 1; DECLARE done INT DEFAULT 0; DECLARE childResult int default 0; DECLARE userID int unsigned; DECLARE cur CURSOR FOR SELECT id FROM core_medlemmer; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; truncate core_stat_totals; open cur; repeat read_loop: LOOP FETCH cur INTO userID; IF done THEN LEAVE read_loop; END IF; call getStats(userID, concat(yr,'-01-01'), @result); insert into core_stat_totals(id, half_year,num_attendance) values(userID, concat('q1_',yr),@result); END LOOP; SET yr = yr + 1; until yr = 1 + year(current_date()) end repeat; CLOSE cur; select * from core_stat_totals; end //
EDIT: I solved a couple of issues with cursor not being declared and unopen cursors, now my procedure keeps returning things, which is essentially the inner procedure returning them, and getting 2014: commands out of sync.
I am working in mysql workbench against Mariadb 10.1.30