MySQL syntax error issue in creating stored procedure

Question

I'm trying to create stored procedure but phpmyadmin saying I've syntax error in it but I can't see any error.

1064 - 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 'CURSOR CUR_ID_FORUM

IS SELECT ForumID FROM Forum' at line 3

delimiter //
CREATE PROCEDURE updateForumAdmin (IN user_id INT, IN previous_role INT,IN new_role INT)
BEGIN 
CURSOR CUR_ID_FORUM 
IS 
SELECT ForumID FROM Forum //
IF(
    previous_role=1,
    DELETE ForumManager WHERE ModuleID=3 AND ModuleEntityID=user_id AND IsDirect=0,
    SELECT ForumID FROM Forum
    FOR REC_ID_FORUM IN CUR_ID_FORUM
    LOOP
        INSERT ForumManager (ForumID,ModuleID,ModuleEntityID,ModuleRoleID,AddedBy,IsDirect) VALUES (REC_ID_FORUM,3,user_id,11,0,0)
    END LOOP //
)
END //
delimiter ;

My updated code :

delimiter //
CREATE PROCEDURE updateForumAdmin (IN user_id INT, IN previous_role INT,IN new_role INT)
BEGIN 
DECLARE REC_ID_FORUM INT(11) //
DECLARE CUR_ID_FORUM CURSOR FOR SELECT ForumID FROM Forum //
IF(
    previous_role=1,
    DELETE ForumManager WHERE ModuleID=3 AND ModuleEntityID=user_id AND IsDirect=0,
    SELECT ForumID FROM Forum
    FOR REC_ID_FORUM IN CUR_ID_FORUM
    LOOP
        FETCH CUR_ID_FORUM INTO REC_ID_FORUM //
        INSERT ForumManager (ForumID,ModuleID,ModuleEntityID,ModuleRoleID,AddedBy,IsDirect) VALUES (REC_ID_FORUM,3,user_id,11,0,0)
    END LOOP //
)
END //
delimiter ;

and phpmyadmin's updated error :

1064 - 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 3

The error tells you what it is, it doesn't like the part starting CURSOR. Where did you get this code as according to this page msdn.microsoft.com/en-gb/library/ms180169.aspx you need to DECLARE cursor_name CURSOR — Peter Featherstone

davidethell davidethell · Accepted Answer · 2017-02-06T12:35:15

UPDATED: Your syntax is incorrect in many places. You need to declare the REC_ID_FORUM variable to use for your cursor fetch:

DECLARE REC_ID_FORUM INTEGER //

You need to use the correct cursor syntax of:

DECLARE CUR_ID_FORUM CURSOR FOR SELECT ForumID FROM Forum //

After you declare your cursor you need to open it:

OPEN CUR_ID_FORUM //

And in your if statement you need to FETCH from the cursor in your LOOP:

FETCH CUR_ID_FORUM INTO REC_ID_FORUM //

Your IF statement syntax is also wrong. You're using the IF function that is used in MySQL select statements, not the IF conditional used in stored procedures.

Here is what I think your entire code should look like:

delimiter //
CREATE PROCEDURE updateForumAdmin (IN user_id INT, IN previous_role INT,IN new_role INT)
BEGIN 
    DECLARE REC_ID_FORUM INTEGER //
    DECLARE CUR_ID_FORUM CURSOR FOR SELECT ForumID FROM Forum //
    IF previous_role = 1 THEN
        DELETE ForumManager WHERE ModuleID=3 AND ModuleEntityID=user_id AND IsDirect=0 //
    ELSE
        OPEN CUR_ID_FORUM //
        LOOP
            FETCH CUR_ID_FORUM INTO REC_ID_FORUM //
            INSERT ForumManager (ForumID,ModuleID,ModuleEntityID,ModuleRoleID,AddedBy,IsDirect) VALUES (REC_ID_FORUM,3,user_id,11,0,0)
        END LOOP //
    END IF //
END //
delimiter ;

This is very much untested but should get you closer.

MySQL syntax error issue in creating stored procedure

1064 - 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 'CURSOR CUR_ID_FORUM

1 Answers