mysql存储过程动态条件查询语句_存储过程中的mysql动态查询

i am creating a dynamic query in 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');

i get the following error message:

Error Code: 1054. Unknown column 'SPA' in 'where clause'

i tested without where condition and it works fine, but with the where condition its not working, i tried using @ with the variable name but it still does not work.

Thanks for your help.

解决方案

You missed the quotes in WHERE clause.

Try like this:

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

Explanation:

Query from your code would be like:

SELECT * FROM Test where team=SPA

And we changed it to:

SELECT * FROM Test where team='SPA'