0

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?

  • Forgive my lack of mysql knowledge, but wouldn't the variable @q1 be considered "out of scope" when used within the call procedure? Also, please lookup the phrase "SQL Injection". – Michael Kutz Sep 19 '20 at 9:32
  • @MichaelKutz, you're probably right. The person with the "lack of mysql knowledge", I'm sure, is me. But again, that is the point of this question: to put the matter to rest, so I don't continue trying to achieve the impossible. – Ifedi Okonkwo Sep 19 '20 at 10:26
  • @ variables are set as the SET (or SELECT) is encountered. The CALL, with its use of @q1 happens before the SET @q1. You must switch the order. But that means rethinking everything. – Rick James Sep 19 '20 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.