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
– User 99x, Commented May 12, 2014 at 8:16

Tom Mac · Accepted Answer · 2014-05-12 08:53:01Z

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, 2014 at 8:53

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

User 99x Over a year ago

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?

Tom Mac Over a year ago

Usually depends on the OS. Unix=yes, Windows & Mac OS=no. Check out this for more info: dev.mysql.com/doc/refman/5.7/en/…

Collectives™ on Stack Overflow

Mysql Stored procedure issue with varchar variable in where clause

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related