在where子句中附加变量 [英] Appending variable in where clause

问题描述

你好，

我有一个名为SEMQueries(busno，qry)的表，其记录如ex:

busno qry
1(SEMColumn =``3''且扩展名=``CS1''或SEMColumn =``4''且扩展名=``CS1'')

现在我有一个SP，如下所示:

Hello,

I have a table named SEMQueries(busno, qry) having records as ex:

busno qry
1 (SEMColumn = ''3'' and Extension = ''CS1'' or SEMColumn = ''4'' and Extension = ''CS1'')

Now I have a SP as below :

<small></small>DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_tstcalc` $$
CREATE PROCEDURE `sp_tstcalc`(in cntslot int)
BEGIN
DECLARE done1 INT DEFAULT 0;
declare busnum int;
declare varqry varchar(5000);
declare result double;

DECLARE c3 cursor for select busno, qry from SEMQueries where busno = 1 order by busno;
DECLARE CONTINUE HANDLER FOR SQLSTATE ''02000'' SET done1 = 1;

OPEN c3;
REPEAT
FETCH c3 into busnum, varqry;
IF NOT done1 THEN
set result = (select sum(reading) from outputcsv where varqry;
insert into testtablemeteravg(Busnumber,val) values (busnum,result);

END IF;
UNTIL done1 END REPEAT;
CLOSE c3;
END $$

DELIMITER ;

现在，在In SP的第Line行:

Now, at In SP, at Line :

set result = (select sum(reading) from outputcsv where varqry;
Above stmt should get evaluated as below :
set result = (select sum(reading) from outputcsv where
(SEMColumn = ''3'' and Extension = ''CS1'' or SEMColumn = ''4'' and Extension = ''CS1''));

但是上面的stmt返回``null''.

stmt中是否有任何问题:

But the above stmt returns ''null''.

Is there any problem in the stmt :

set result = (select sum(reading) from outputcsv where varqry;
Here variable ''varqry'' contains : (SEMColumn = ''3'' and Extension = ''CS1'' or SEMColumn = ''4'' and Extension = ''CS1'')

我想变量"varqry"没有正确附加到where子句中. 为什么会发生这种情况?


谢谢n问候，

I suppose variable ''varqry'' isnt proper appending to where clause..
Why does this happen ?


Thanks n Regards,

推荐答案

如果存在sp_tstcalc`，则丢弃程序

DROP PROCEDURE IF EXISTS `sp_tstcalc`

创建过程sp_tstcalc`(在cntslot int中) 开始 DECLARE done1 INT默认值0; 声明busnum int; 声明varqry varchar(5000); 宣告结果加倍； DECLARE c3光标，用于从SEMQueries中选择busno，qry，其中busno = 1(按busno排序)； SQLSTATE``02000''的DECLARE继续处理程序SET done1 = 1; 打开c3; 重复 将c3抓取到busnum，varqry中； 如果未完成1则 设置结果=(从outputcsv中选择sum(reading)，其中varqry; 插入testtablemeteravg(Busnumber，val)值(busnum，result); 万一; 直到完成1 END REPEAT； 关闭c3; END

CREATE PROCEDURE `sp_tstcalc`(in cntslot int) BEGIN DECLARE done1 INT DEFAULT 0; declare busnum int; declare varqry varchar(5000); declare result double; DECLARE c3 cursor for select busno, qry from SEMQueries where busno = 1 order by busno; DECLARE CONTINUE HANDLER FOR SQLSTATE ''02000'' SET done1 = 1; OPEN c3; REPEAT FETCH c3 into busnum, varqry; IF NOT done1 THEN set result = (select sum(reading) from outputcsv where varqry; insert into testtablemeteravg(Busnumber,val) values (busnum,result); END IF; UNTIL done1 END REPEAT; CLOSE c3; END

DELIMITER;

DELIMITER ;

现在，在In SP的第Line行:

Now, at In SP, at Line :

set result = (select sum(reading) from outputcsv where varqry;
Above stmt should get evaluated as below :
set result = (select sum(reading) from outputcsv where
(SEMColumn = ''3'' and Extension = ''CS1'' or SEMColumn = ''4'' and Extension = ''CS1''));

但是上面的stmt返回``null''.

stmt中是否有任何问题:

But the above stmt returns ''null''.

Is there any problem in the stmt :

set result = (select sum(reading) from outputcsv where varqry;
Here variable ''varqry'' contains : (SEMColumn = ''3'' and Extension = ''CS1'' or SEMColumn = ''4'' and Extension = ''CS1'')

我想变量"varqry"没有正确附加到where子句中. 为什么会发生这种情况?


谢谢您，

I suppose variable ''varqry'' isnt proper appending to where clause..
Why does this happen ?


Thanks n Regards,

这篇关于在where子句中附加变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！