Appending variable in where clause

Question

5.00/5 (1 vote)

See more:

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 ;

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'));

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')

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

Thanks n Regards,

Posted 18-Feb-11 22:37pm

shwetavc30

Add a Solution

2 solutions

Solution 2

SEMColumn = '3' and Extension = 'CS1' or SEMColumn = '4' and Extension = 'CS1')
You might want some brackets for the two and-or conditions here.

Posted 18-Feb-11 23:15pm

Abhinav S

v2

Comments

shwetavc30 19-Feb-11 5:33am

Hello,
My Question is :
1) set result = (select sum(reading) from outputcsv where varqry;

2) variable 'varqry' contains : (SEMColumn = '3' and Extension = 'CS1' or SEMColumn = '4' and Extension = 'CS1')

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

My Question is : 2) stmt when excuted runs properly giving results. but 1) Query doent give any results.

Is this the problem because varqry is a variable ?

Thanks n Regards,

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-02-18T23:14:00

Hi,

Could you simplify this to a single statement. Something like:

set result = (select sum(reading) 
              from outputcsv 
              where (SEMColumn, Extension) IN (SELECT  busno, qry 
                                               from SEMQueries 
                                               where busno = 1 ))

I'm not sure if MySQL is capable of doing multicolumn IN clause but you could try.

Also instead of literal values try to use bind variables for busno condition unless it's really a constant.