MYSQL stored procedure: read value from one table and use it in WHERE on another-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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