mysqlstored-proceduressyntax-errordeclare

"DECLARE is not valid at this position, expecting END" SQL syntax error


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.


Solution

  • 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.