Re: Stored Procedure returning what I didn't expected

[Peter Brawley]

If a query is composed of variables which themselves have SQL, you have to use PREPARE, q.v in the manual.

Also ...

SET CodigoIn = (SELECT padre FROM categoria WHERE codigo = CodigoIn);

is unnecessarily prolix & inefficient. All you need do is prepare a list eg ...

drop procedure if exists p;
delimiter go
create procedure p()
begin
  set @i=1, @n=2, @list='';
  while @i<=@n do
    set @list=concat(@list,@i);
    if @i<@n then
      set @list=concat(@list,',');
    end if;
    set @i=@i+1;
  end while;
  set @sql = concat("select i from (select 1 as i union select 2 union select 3)x where i in(",@list,")");
  prepare stmt from @sql;
  execute stmt;
  drop prepare stmt;
end;
go
delimiter ;