I have this procedure into which I need to pass variable parameters. Problem is this: once inside the procedure, the
IN parameter needs to pick up already created variables in order to create the final query.
DELIMITER $$ DROP PROCEDURE IF EXISTS MY_PROCEDURE $$ CREATE PROCEDURE MY_PROCEDURE ( IN sql_string TEXT ) BEGIN SET @q1 := "SELECT * FROM table1"; SET @q2 := "SELECT * FROM table2"; -- capturing the string of the query from input SET @sql = sql_string; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; -- calling the procedure call MY_PROCEDURE('CONCAT(@q1, " WHERE id1 < 10")'); call MY_PROCEDURE('CONCAT(@q1, " WHERE id1 < 10", " UNION ", @q2, " WHERE id2 < 10")');
No matter what I've tried, supplying the
@ variable with the
IN string results in a syntax error.
I have considered that having 2 groups of
PREPARE might do the trick:
First PREPARE converts the
IN string to actual query by expanding variables, only return the string (Is this even possible???);
Second PREPARE then runs the query on the expanded string.
Phew!!! Any help?