Want to Avoid Cursor
I am trying to write an sp that has a cursor in it. This sp is complex:
the sp itself is written in one database; however it accesses multiple other databases and this needs to be done dynamically, which means that the definition of the cursor has to become dynamic. ok, so I have this now as my sp definition:
CREATE PROCEDURE `Home_kids` (IN homeroom varchar(4), IN school varchar(30),
OUT lname varchar(10), OUT fname varchar(10), OUT d_bdate date, OUT program_code varchar(10), OUT d_previous_school varchar(10), OUT hr varchar(10), OUT d_date date, OUT d_engagement varchar(10), OUT d_fluency varchar(10), OUT d_accuracy varchar(10), OUT d_comprehension varchar(10),OUT d_overall varchar(10), OUT d_fiction varchar(10), OUT d_stage varchar(10), OUT d_cycle varchar(10))
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE program_code varchar(30) DEFAULT 'None';
DECLARE d_previous_school char(4);
DECLARE hr, lname, fname, d_engagement, d_fluency, d_accuracy, d_comprehension, d_overall,d_fiction, d_stage, d_cycle varchar(10);
DECLARE d_date, d_bdate date;
DECLARE not_found BOOLEAN DEFAULT false;
DECLARE scho varchar(10);
DECLARE cur_1 CURSOR FOR
select last_name,
given_name,
birth_date,
program,
scho.name,
division,
dra_date,
dra_engagement,
dra_fluency,
dra_accuracy,
dra_comprehension,
dra_overall,
dra_fiction,
dra_stage,
dra_cycle
from school.stud
left join dublin_dra on id=student_id
left join scho on previous_school = scho.school_id
where division like concat(hr,'%') order by division, last_name, birth_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND
set not_found = TRUE;
OPEN cur_1;
REPEAT
FETCH cur_1 into
lname,
fname,
d_bdate,
program_code,
d_previous_school,
hr,
d_date,
d_engagement,
d_fluency,
d_accuracy,
d_comprehension,
d_overall,
d_fiction,
d_stage,
d_cycle;
UNTIL not_found = TRUE
END REPEAT;
CLOSE cur_1;
this compiles fine if I change the school in the from clause of the cursor definition and replace it with a real database name; however, i really can't do that because the selection of the db is dynamic and in the hands of the user not the application.
i believe prepared statements do not work in cursors. can anyone suggest a solution to this problem?
thanks much,
shawn