Syntax error for DECLARE CURSOR FOR

Question

I do not understand why I am getting a syntax error in my sp code below. Can someone help me figure this out?

SQL Error (1064):

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to 'DECLARE CUR1 CURSOR FOR SELECT pc.prospectus_courses_id FROM prereq_cou' on line 8

DELIMITER $$ DROP PROCEDURE IF EXISTS get_prereqs3$$ CREATE PROCEDURE get_prereqs3(IN prosp_courses_id SMALLINT(5)) BEGIN DECLARE done int DEFAULT FALSE; DECLARE required SMALLINT(5) default 0; DECLARE to_search SMALLINT(5) default 0; DROP TABLE IF EXISTS tmp_list; CREATE TABLE tmp_list(courses_id SMALLINT(5), courses_id_req SMALLINT(5)) ENGINE = MEMORY; DECLARE CUR1 CURSOR FOR SELECT pc.prospectus_courses_id FROM prereq_courses pc JOIN prerequisites pr on (pr.id = pc.prerequisites_id) JOIN prospectus_courses ps on (ps.id = pr.prospectus_courses_id) WHERE ps.id = to_search MAIN_LOOP: LOOP DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur1; FETCH cur1 INTO required; IF done THEN CLOSE cur1; LEAVE main_loop; ELSE insert into tmp_list values (to_search, required); set to_search = required; iterate main_loop; END IF; END LOOP; select c.course_code from tmp_list t join prospectus_courses pc on pc.id = t.courses_id_req join courses c on c.id = pc.courses_id ; drop table tmp_list; END$$ DELIMITER ; 

Answer 1

Ads must be immediately after the BEGIN block. In your case, just move DECLARE cur1 CURSOR and DECLARE CONTINUE HANDLER.. two lines up.

Sometimes you want to declare a variable or cursor later in the code, for example, only if the condition is met.

In this case, you can reinsert the block with the nested BEGIN .. END .

http://dev.mysql.com/doc/refman/5.5/en/begin-end.html and http://dev.mysql.com/doc/refman/5.5/en/declare.html

DECLARE is only allowed inside the compound BEGIN ... END statement and must be at the beginning, before any other statements.

You also declare CUR1 , but use CUR1 .

Answer 2

Is there a semicolon?

 WHERE ps.id = to_search; ^___________