如何在使用动态where子句的同时使用mysql中的execute语句

Question

Below is my store procedure .please help........



BEGIN



DECLARE selectQuery VARCHAR(2000);
declare finalquery varchar(2000);
declare stmt3 varchar(2000);

SET selectQuery = 'SELECT tbl_property.intId, strAddressLine1,(select strItemName from tbl_lk_item where intId=tbl_property.intPropertyCountyTypeId) as strCountyName ,(select strItemName from tbl_lk_item where intId=tbl_property.intPropertyCountryTypeId) as strCountryName ,strpostCode,(tbl_pro_adver_matchcriteria.floatAskingPrice),tbl_pro_adver_matchcriteria.intBedrooms
FROM tbl_property LEFT OUTER JOIN tbl_pro_adver_matchcriteria on tbl_property.intId = tbl_pro_adver_matchcriteria.intPro
set finalquery =CONCAT(selectQuery,strSqlQuery,' AND tbl_property.intId=1 ');  

execute finalquery;

END

当我运行存储过程并传递参数'where tbl_property.intId=1‘时，它给出了过程执行失败1243 -给定要执行的未知预准备语句处理程序(finalquery)

我通过select语句检查查询结果，它给出了正确的查询并返回result.So，请帮助我使用Execute语句。

Answer 1

谢谢你的帮助，我已经尝试过了，它和小modifications..below一起工作是我的存储过程：

BEGIN
DECLARE selectQuery VARCHAR(2000);
declare finalquery varchar(2000);

SET selectQuery = 'SELECT tbl_property.intId, strAddressLine1,(select strItemName from tbl_lk_item where intId=tbl_property.intPropertyCountyTypeId) as strCountyName ,(select strItemName from tbl_lk_item where intId=tbl_property.intPropertyCountryTypeId) as strCountryName ,strpostCode,(tbl_pro_adver_matchcriteria.floatAskingPrice),tbl_pro_adver_matchcriteria.intBedrooms
FROM tbl_property LEFT OUTER JOIN tbl_pro_adver_matchcriteria on tbl_property.intId = tbl_pro_adver_matchcriteria.intProId ';

set @finalquery =CONCAT(selectQuery,strSqlQuery,' AND tbl_property.intId=1 ');  

PREPARE result from @finalquery;
EXECUTE result;
DEALLOCATE PREPARE result;

END

Answer 2

尝试此链接http://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html，它可能会帮助您，开始

DECLARE selectQuery VARCHAR(2000);
declare finalquery varchar(2000);
declare stmt3 varchar(2000);

SET selectQuery = 'SELECT tbl_property.intId, strAddressLine1,(select strItemName from tbl_lk_item where intId=tbl_property.intPropertyCountyTypeId) as strCountyName ,(select strItemName from tbl_lk_item where intId=tbl_property.intPropertyCountryTypeId) as strCountryName ,strpostCode,(tbl_pro_adver_matchcriteria.floatAskingPrice),tbl_pro_adver_matchcriteria.intBedrooms
FROM tbl_property LEFT OUTER JOIN tbl_pro_adver_matchcriteria on tbl_property.intId = tbl_pro_adver_matchcriteria.intProId ';

set finalquery =CONCAT(selectQuery,strSqlQuery);  


EXECUTE finalquery;

Answer 3

动态语句必须在使用后进行准备和释放。下面是一个例子。

BEGIN

SET @selectQuery = 'SELECT * from table1 where'

SET @QUERY = CONCAT(@selectQuery, ' field = 1');            
SELECT @QUERY;
PREPARE s FROM @QUERY;

EXECUTE s;
DEALLOCATE PREPARE s;

END