存储过程中的mysql动态查询 [英] mysql dynamic query in stored procedure
本文介绍了存储过程中的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');
我收到以下错误消息:
错误代码:1054."where子句"中的未知列"SPA"
Error Code: 1054. Unknown column 'SPA' in 'where clause'
我在没有where条件的情况下进行了测试,它可以正常工作,但是在where条件不起作用的情况下,我尝试将@与变量名一起使用,但仍然无法正常工作.
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.
感谢您的帮助.
推荐答案
您错过了WHERE
子句中的引号.
You missed the quotes in WHERE
clause.
尝试这样:
SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'");
说明:
从您的代码中查询将是:
Query from your code would be like:
SELECT * FROM Test where team=SPA
我们将其更改为:
SELECT * FROM Test where team='SPA'
这篇关于存储过程中的mysql动态查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文