How to Use Limit Clause in MySQL Procedure

well, there is some trouble when you want to use limit clause with input parameter in Store Prosedure. Since July 2005, Markus Popp was report this problem as bug and also request for new feature. until now, MySQL didn't support yet this feature.

as an alternative solution, use prepare statement to identifiy limit variable like this following code:

delimiter //
drop procedure if exists limitTest //
create procedure limitTest( idin int )
begin
set @sql = concat('select * from mytable order by id limit ', idin );
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
end //
delimiter ;
call limittest(5);


related documents:
http://bugs.mysql.com/bug.php?id=11918
http://forums.mysql.com/read.php?98,28212
http://bugs.mysql.com/bug.php?id=33856