MySQL Syntax Error - END IF line

Question

I try to create a stored procedure with an if statement within. I copied from: https://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html

But I get the following error exact on the END IF; near '':

DROP PROCEDURE IF EXISTS `myProc`; 
CREATE DEFINER=`root`@`%` PROCEDURE `myProc`(
   IN in_userId int,
   IN in_projectId int
)
BEGIN
    DECLARE tmp_courseId int;
    DECLARE done TINYINT DEFAULT 0;
    DECLARE cursorProjectCourse CURSOR FOR SELECT CourseId FROM XC_PROJECT_COURSE where projectId = in_projectId ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursorProjectCourse;

    read_loop: LOOP

        FETCH FROM cursorProjectCourse INTO tmp_courseId;

        IF done = 1 THEN LEAVE read_loop;

        END IF;

        SELECT tmp_courseId, in_userId; 
    END LOOP;
    CLOSE cursorProjectCourse;
END;

Has anyone an idea where I make a mistake?

Exact error message:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 19 SQL Error

MySQL Version: 5.5.46

Thanks for help!

Norbert Koch · Accepted Answer · 2017-07-12 11:34:08Z

I found the solution. I have to set DELIMITER $$ at first statement and at the end DELIMITER ;

DELIMITER $$;
DROP PROCEDURE IF EXISTS `myProc`; $$
CREATE DEFINER=`root`@`%` PROCEDURE `myProc`(
   IN in_userId int,
   IN in_projectId int
)
BEGIN
    DECLARE tmp_courseId int;
    DECLARE done TINYINT DEFAULT 0;
    DECLARE cursorProjectCourse CURSOR FOR SELECT CourseId FROM XC_PROJECT_COURSE where projectId = in_projectId ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursorProjectCourse;

    read_loop: LOOP

        FETCH FROM cursorProjectCourse INTO tmp_courseId;

        IF done = 1 THEN LEAVE read_loop;

        END IF;

        SELECT tmp_courseId, in_userId; 
    END LOOP;
    CLOSE cursorProjectCourse;
END;$$
DELIMITER ;

It is important to set the keyword on the first position in line. If there is a blank on the first position, the error above will be thrown.

Rahul · Accepted Answer · 2017-07-12 10:53:53Z

0

Well you are missing the loop label while ending loop. Change it to below

read_loop: LOOP
    FETCH FROM cursorProjectCourse INTO tmp_courseId;
    IF done = 1 THEN 
    LEAVE read_loop;
    END IF;
END LOOP read_loop;
    SELECT tmp_courseId, in_userId;

edited Jul 12, 2017 at 10:53

answered Jul 12, 2017 at 10:40

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

3 Comments

Norbert Koch Over a year ago

I tried also other solutions to position the END IF statement (like the same line etc.) but it is still not working.

Rahul Over a year ago

@NorbertKoch, disregard previous answer ... see edit now. Hope it helps.

Norbert Koch Over a year ago

Thanks. I found the solution above. After this it works with both syntax: END LOOP; and END LOOP read_loop;

Collectives™ on Stack Overflow

MySQL Syntax Error - END IF line

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related