Rusty Razor Blade
After banging my head against a wall for a while, I finally found this thread from June 2005 in the MySQL support forums regarding using variables in the LIMIT clause. They don’t support it yet. There’s no time table that indicates when it will be supported either. It’s very unfortunate, I was looking forward to benchmarking the difference between our ad hoc code and the stored proc version.
This code will not work:
SELECT field from table LIMIT limit_var
Very unfortunate.
If you enjoyed this article, please consider sharing it!
you can prepare an statement and then execute it with a variable limit, e.g.,
CREATE PROCEDURE `All`(in off int, in lmt int)
BEGIN
set @a = off;
set @b = lmt;
prepare stmt from “select * from table1 limit ?,?”;
execute stmt using @a,@b;
END $$
Sorry but according the manual you cannot use prepare or execute instructions on a procedure or function
what can I say? give it a try, it is working for me on a MySQL 5.0.37 version server running on Windows