Mysql Stored procedure issue with varchar variable in where clause

Question

I have a problem in mysql stored procedures where the varchar variable in the where clause doesn't return the results. The query is given below.

declare itcode varchar(30);
declare qty int;
declare finished int;
declare testc cursor for 
    select itemcode from mytable limit 0,10;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
open testc;
read_loop:Loop
    fetch testc into itcode;
    if finished=1 then
        leave read_loop;
    end if;
    select sum(Qty) as total from 
            mytable2 
            where itemcode=itcode;
end loop;
close testc;

In the above statement It returns null even though the item code exists on both tables. however if I write the statement with the manually assigned value on the where close as below it works.

select sum(Qty) as total from mytable2 where itemcode='p2343';

I'm unable to figure out why the varchar variable doesn't work on the where clause. Can someone let me help me to figure out how to resolve this type issue?

NOTE: Both tables columns are varchar(30).

Additional Note: When I change the statement as below, it prints the values in the itcode as well.

select sum(Qty) as total,itcode from mytable2 where itemcode=itcode

So the itcode have the value 'p2343' but the above stored procedure is not working.

yes, when I give as string with single quotes it works. But when i assign the variable it didn't work. — User 99x, May 12 '14 at 08:16
When I type as "select sum(Qty) as total,itcode from mytable2 where itemcode=itcode" it prints the values of the itcode. So it has the values. — User 99x, May 12 '14 at 08:21
I'm not sure, but you can try declaring variable preceding with `@` like, `@itcode` — Ravi Dhoriya ツ, May 12 '14 at 08:22
Tired that too. Are there any other ways to resolve this issue? — User 99x, May 12 '14 at 08:36
What if you specify charset **itcode varchar(30) CHARACTER SET utf8**? — StanislavL, May 12 '14 at 08:39
Tried the itcode varchar(30) CHARACTER SET utf8, didn't work. Is there anyway to convert the sql string to execute and get the result to the variable? Then I should be able to get a result i guess. — User 99x, May 12 '14 at 08:50

score 2 · Accepted Answer · answered May 12 '14 at 08:53

2

Problem here is that the procedure is referencing your global variable qty in favour of the Qty column on your mytable2. Try changing this:

declare qty int;

to this

declare v_qty int;

answered May 12 '14 at 08:53

Tom Mac

9,693
3
25
35

thanks alot. I was googling all over the place to resolve this. Just to understand, wont the mysql identify the case differences in the field and variable? – User 99x May 12 '14 at 09:31
Usually depends on the OS. Unix=yes, Windows & Mac OS=no. Check out this for more info: http://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html – Tom Mac May 12 '14 at 10:24

Mysql Stored procedure issue with varchar variable in where clause

1 Answers1