Why Mysql always returns zero value withing stored procedure?

Question

I use following to reserve id if not exist in table;

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_ch_id_from_ych_id`(pYch_id nvarchar(100), pIP_address nvarchar(30))
BEGIN
declare ch_id bigint;
set ch_id =(select ifnull(max(ch_id),0)ch_id from channels where ych_id=pYch_id);
if(ch_id<1) then 
    INSERT INTO channels
    (ych_id, Name, Last_IP, Active, Banned, Qualified, ReportedCount)
    VALUES
    (pYch_id,Name,pIP_address,1      ,0     ,0        ,0);
end if;
select ifnull(max(ch_id),0) ch_id from channels where ych_id=pYch_id;
END

However, it always return zero when called as below;

call get_ch_id_from_ych_id ('A',''); //not work
select ifnull(max(ch_id),0) ch_id from channels where ych_id='A'; //works    

Data row is inserted without problem and exist in table. but in first line, it returns zero. when I run sql in workbench it returns correct value. What is wrong here?

Answer 1

Don't give you local variable the same name as a table column. When you refer to ch_id in the query, it uses the variable rather than the column.

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_ch_id_from_ych_id`(pYch_id nvarchar(100), pIP_address nvarchar(30))
BEGIN
declare temp_ch_id bigint;
set temp_ch_id =(select ifnull(max(ch_id),0)ch_id from channels where ych_id=pYch_id);
if(temp_ch_id<1) then 
    INSERT INTO channels
    (ych_id, Name, Last_IP, Active, Banned, Qualified, ReportedCount)
    VALUES
    (pYch_id,Name,pIP_address,1      ,0     ,0        ,0);
end if;
select ifnull(max(ch_id),0) ch_id from channels where ych_id=pYch_id;
END