skip to Main Content

I’m trying to create a simple stored procedure that reads IDs from one table and deletes records that reference these IDs from another. I have a cursor which works fine, the problem is using that cursor in WHERE clause (for development purposes I’m using SELECT instead of DELETE):

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id CHAR(30);
DECLARE cur1 CURSOR FOR SELECT idea_id FROM projects WHERE DATEDIFF(DATE(NOW()), DATE(last_update)) > 14;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  read_loop: LOOP
    FETCH cur1 INTO id;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @A:= CONCAT('select * from stats where idea = "',id,'"'); 
    Prepare stmt FROM @A;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur1;
END

The problem is, that last select from the stats table always returns 0 results, even though the results are there if I manually get IDs from the cursor and paste them into the WHERE clause on stats. I suspect I’m not using the variable in the prepared statement correctly but I can’t find a solution. Please help.

2

Answers


  1. Chosen as BEST ANSWER

    Nevermind, found the issue: the type of id declared in the procedure didn't match the type of idea_id field in the projects table and it was quietly truncated and then obviously no results matched. Please close.


  2. I had the same issue as you, just set done to false after each loop

    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE id CHAR(30);
        DECLARE cur1 CURSOR FOR SELECT idea_id FROM projects WHERE DATEDIFF(DATE(NOW()), DATE(last_update)) > 14;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
          OPEN cur1;
          read_loop: LOOP
            FETCH cur1 INTO id;
            IF done THEN
              LEAVE read_loop;
            END IF;
        
            SET @A:= CONCAT('select * from stats where idea = "',id,'"'); 
            Prepare stmt FROM @A;  
            EXECUTE stmt;  
            DEALLOCATE PREPARE stmt;
           
           SET done = FALSE;
          END LOOP;
          CLOSE cur1;
        END
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search