#this will not compile on copy-paste into terminal

DROP PROCEDURE IF EXISTS nest_test//
CREATE PROCEDURE nest_test (IN comma_del_list VARCHAR(500))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE test_key, count INT;
DECLARE result_value DOUBLE;

DECLARE c1 CURSOR FOR SELECT id FROM list_to_array;
DECLARE c2 CURSOR FOR SELECT value FROM result WHERE test_app_id = test_key;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

DROP TEMPORARY TABLE IF EXISTS list_to_array;
CREATE TEMPORARY TABLE list_to_array(id INT);

DROP TEMPORARY TABLE IF EXISTS table_two;
CREATE TEMPORARY TABLE table_two (value_one DOUBLE);

CALL list_to_array(comma_del_list, ',');

OPEN c1;
REPEAT
FETCH c1 INTO test_key;
IF NOT done THEN

	OPEN c2;
	REPEAT FETCH c2 INTO result_value;
	
	IF NOT done THEN
		INSERT INTO table_two (value_one) VALUES ('1');
	END IF;
	UNTIL done END REPEAT;
	close c2;
	SET done = 0;

END IF;
UNTIL done END REPEAT;
CLOSE c1;

SELECT * FROM table_two;
END
//


#this *will* compile on copy-paste into terminal

DROP PROCEDURE IF EXISTS nest_test//
CREATE PROCEDURE nest_test (IN comma_del_list VARCHAR(500))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE test_key, count INT;
DECLARE result_value DOUBLE;

DECLARE c1 CURSOR FOR SELECT id FROM list_to_array;
DECLARE c2 CURSOR FOR SELECT value FROM result WHERE test_app_id = test_key;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

DROP TEMPORARY TABLE IF EXISTS list_to_array;
CREATE TEMPORARY TABLE list_to_array(id INT);

DROP TEMPORARY TABLE IF EXISTS table_two;
CREATE TEMPORARY TABLE table_two (value_one DOUBLE);

CALL list_to_array(comma_del_list, ',');

OPEN c1;
REPEAT
FETCH c1 INTO test_key;
IF NOT done THEN

OPEN c2;
REPEAT FETCH c2 INTO result_value;

IF NOT done THEN
	INSERT INTO table_two (value_one) VALUES ('1');
END IF;
UNTIL done END REPEAT;
close c2;
SET done = 0;

END IF;
UNTIL done END REPEAT;
CLOSE c1;

SELECT * FROM table_two;
END
//