CODEWITHSUNDEEP
mysqlstored-procedures
Mohit Bumb
Mohit Bumb

Reputation: 2493

MySQL syntax error issue in creating stored procedure

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

Upvotes: 1

Views: 1733

Answers (1)

davidethell
davidethell

Reputation: 12018

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.

Upvotes: 2

Related Questions