Hi I am trying to write a stored procedure which will execute a query and if resultset is not empty results will be inserted into a table passed as the second parameter to the procedure.
Below is what I wrote but I am having error with declaring the cursor, any help about the code will be very helpful.
Thanks,
Delimiter //
CREATE PROCEDURE evaluateSQL(select_sql TEXT, table_name TEXT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE query2 text;
DECLARE res text;
DECLARE cur1 CURSOR FOR select_sql;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
FETCH cur1 INTO res;
IF done THEN
SET @query2 = CONCAT("CREATE TABLE ", table_name, "(A1 VARCHAR(100))");
PREPARE stmt FROM @query2;
EXECUTE stmt;
insert_loop: LOOP
SET @query3 = CONCAT("INSERT INTO", table_name, "VALUES(", res,")");
PREPARE stmt1 from @query3;
execute stmt1
FETCH cur1 INTO res;
IF done THEN
LEAVE insert_loop;
END IF;
END LOOP insert_loop;
END IF;
CLOSE cur1;
END//
delimiter ;