CODEWITHSUNDEEP
phpmysqlstored-procedures
Mohit Bumb
Mohit Bumb

Reputation: 2493

How to write multiple queries in stored procedure

What I'm trying to do is when user roles changed to 1 or from 1 then delete all his entries from ForumManager table which IsDirect value is 0 or if its value changes to 1 then I need to insert person's entry to ForumManager table for all ForumID.

But for inserting it I have to run 1 more queries to get ForumID and insert in ForumManager with loop.

What I try till now is incomplete and maybe not correct or right way I don't know how to run loop inside stored procedure.

delimiter //
CREATE PROCEDURE update_forum_admin (IN user_id INT, IN previous_role INT,IN new_role INT)
BEGIN
if(previous_role == 1)
{
    DELETE ForumManager WHERE UserID=user_id AND IsDirect=0
}
elseif(new_role == 1) 
{
    SELECT ForumID FROM Forum
    INSERT ForumManager (ForumID,UserID,IsDirect) VALUES (forum_id,user_id,0)
}
END//
delimiter ;

Upvotes: 0

Views: 824

Answers (3)

BuggyCode
BuggyCode

Reputation: 1

its quiet late but Mohit you can get rid of syntax error by modifying code like

CREATE PROCEDURE update_forum_admin (IN user_id INT, IN previous_role INT,IN new_role INT)
BEGIN
if(previous_role = 1)
BEGIN
    DELETE ForumManager WHERE UserID=user_id AND IsDirect=0
END
if(new_role = 1) 
BEGIN
    SELECT ForumID FROM Forum
    INSERT ForumManager (ForumID,UserID,IsDirect) VALUES (forum_id,user_id,0)
END

Also I Can see when you delete a user from ForumManager you do not delete for particular Forum. May be you have only one Forum and if that is the case then take top 1 Forum Id from Forum table

Upvotes: 0

Ihtsham Minhas
Ihtsham Minhas

Reputation: 1515

If you have a column name user_id in Forum table than rename the parameter from user_id to userId. 

delimiter //
 CREATE PROCEDURE update_forum_admin (IN userId INT, IN previous_role INT,IN  new_role INT)
BEGIN
if(previous_role == 1)
{
   DELETE ForumManager WHERE UserID=userId AND IsDirect=0
}
elseif(new_role == 1) 
{

   INSERT ForumManager (ForumID, UserID, IsDirect)  
   SELECT ForumID, userId, 0 FROM Forum group by ForumID, userId, 0
}
END//
delimiter ;

Upvotes: 1

Milaci
Milaci

Reputation: 515

You can declare a coursor with ID_forum and iterate it inside an elseif statement like below(is a pseudo code, if you have e problem to implement it let me know): 

      CREATE PROCEDURE update_forum_admin (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 UserID=user_id AND IsDirect=0
    }
    elseif(new_role == 1) 
    {
          FOR REC_ID_FORUM IN CUR_ID_FORUM
          LOOP
            INSERT ForumManager (ForumID,UserID,IsDirect) VALUES    (REC_ID_FORUM,user_id,0)
          END LOOP;   
    }
    END update_forum_admin  ;

Upvotes: 0

Related Questions