I am currently attempting to create a procedure that will assign a new date to course sessions depending on the code provided. However, I have this syntax error where I am being told "DECLARE" is not valid at this position, expecting END" on the line where the cursor declaration is made.
DELIMITER $$
CREATE PROCEDURE course_schedulings(IN input_course_code CHAR(3), IN scheduling_date DATE)
BEGIN
DECLARE session_code CHAR(3);
SET session_code = (SELECT `code` FROM module WHERE module.course_code = input_course_code);
DECLARE scheduling_cursor CURSOR FOR
SELECT `date` FROM `session` WHERE `code` = input_course_code;
DECLARE complete BOOLEAN DEFAULT FALSE;
END $$
DELIMITER ;
I am really confused on what I am needing to fix here. I assume It has something to do with my cursor or delimiters? I am also aware the logic used throughout the procedure is faulty but I don't see how that would result in a syntax error.
I have tried playing around with the delimiters however did not result in much.
From what I can see, the DECLARE
position is not valid. Try with DECLARE xxxy ...
, DECLARE xxxyz CURSOR ...
then SET xxxy
. Something like this:
DELIMITER $$
CREATE PROCEDURE course_schedulings(IN input_course_code CHAR(3), IN scheduling_date DATE)
BEGIN
DECLARE session_code CHAR(3);
DECLARE complete BOOLEAN DEFAULT FALSE;
DECLARE scheduling_cursor CURSOR FOR
SELECT `date` FROM `session` WHERE `code` = input_course_code;
SET session_code = (SELECT `code` FROM module WHERE module.course_code = input_course_code);
END $$
DELIMITER ;
And see what happen.