Prepare a list of values

from the Artful MySQL Tips List

Example of how to prepare a list of values for a PREPARE query:
drop procedure if exists p;
delimiter go
create procedure p()
  set @i=1, @n=2, @list='';
  while @i<=@n do
    set @list=concat(@list,@i);
    if @i<@n then
      set @list=concat(@list,',');  -- add item to list
    end if;
    set @i=@i+1;                    -- if not at the end, append a comma
  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;
delimiter ;

Last updated 3 Oct 2014

Return to the Artful MySQL Tips page