Mysql dynamic query in stored procedure

Question

I am creating a dynamic query in a stored procedure. my stored procedure is as follows:

CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40)) BEGIN SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team=",w_team); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END 

when I try to run it with the following call:

 call test1 ('Test','SPA'); 

The following error message will appear:

Error code: 1054. Unknown column "SPA" in the section "where where"

i checked without condition, and it works fine, but on condition that it doesn't work, I tried using @ with the variable name, but it still doesn't work.

Thank you for your help.

Answer 1

Try it like this:

 SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'"); 

Explanation

The previous dynamic query would look like this:

 SELECT * FROM Test where team=SPA 

And we changed it to:

 SELECT * FROM Test where team='SPA' 

Answer 2

Try it.

 CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40)) BEGIN SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'"); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END 

You are missing quotes around the w_team variable ..

you must print an expression that is dynamically created so that you can simply copy the printed instructions and try so that you can easily find this problem.

select @ t1 will print a statute that will dynamically build ..

Answer 3

Error code: 1054. Unknown column "SPA" in the section "where where"

This happens when you do not insert the input string in quotation marks, and the SQL engine tries to identify it as a column in the requested table. But he fails because he cannot find him.

But what happens when he finds such a column?
It retrieves the results if it finds some matches of the column values.
Obviously, this is not what they expected.

How to overcome this? Use prepared expressions with dynamic input values.

In stored procedures can you also use a placeholder, for example ? , for dynamic input values ​​for use with Prepared Statements . The engine will process escape characters and other string values ​​when assigning or comparing in SQL expressions.

You just need to reassign the procedure entry to one or more session variables, if necessary.

An example of your procedure :

 CREATE PROCEDURE `test1`( IN tab_name VARCHAR(40), IN w_team VARCHAR(40) ) BEGIN SET @t1 = CONCAT( 'SELECT * FROM ', tab_name, ' where team = ?' ); -- <-- placeholder SET @w_team := w_team; PREPARE stmt3 FROM @t1; EXECUTE stmt3 USING @w_team; -- <-- input for placeholder DEALLOCATE PREPARE stmt3; END;