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.

Answer 1

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;