I går och i dag har jag arbetat med att försöka få till en procedur i MySQL som lägger till data i en tabell baserat på svaret på en Query.
Först blev jag tvungen att skapa en cursor sedan hitta en workaround på felmeddelandet i rubriken.

Resultatet:

DROP PROCEDURE IF EXISTS `my_procedure`;
DELIMITER $$
CREATE PROCEDURE `my_procedure`(in_user_id INT,in_stat_id INT,in_id INT)
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE a,c INT;
DECLARE b VARCHAR(1);
DECLARE cur1 CURSOR FOR SELECT id,limit FROM t1 WHERE id2=in_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @statid = in_stat_id;
SET @userid = in_user_id;
OPEN cur1;
REPEAT
FETCH cur1 INTO a,c;
IF NOT done THEN
SET @limit = c;
SET @id = a;
IF a < c THEN
set @sql2= ‘INSERT INTO t2 (statID,userID,id,alt) SELECT @statid,@userid,t3.id,t3.alternative FROM (SELECT id FROM t3 WHERE id = @id GROUP BY id2 ORDER BY RAND() LIMIT ?) AS A JOIN t2 ON t2.id=A.id’;
prepare stmt2 from @sql2;
execute stmt2 USING @limit;
DEALLOCATE PREPARE stmt2;
ELSE
set @sql2= ‘INSERT INTO t2 (statID,userID,id,alt) SELECT @statid,@userid,t3.id,t3.alternative FROM (SELECT id FROM t3 WHERE id = @id GROUP BY id2 ORDER BY RAND() LIMIT ?) AS A JOIN t2 ON t2.id=A.id’;
prepare stmt2 from @sql2;
execute stmt2 USING @limit;
DEALLOCATE PREPARE stmt2;
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END
$$

Fungerar