MySQL does not support variables with LIMIT in stored procedures
May 17, 2007 – 7:30 pmAfter 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.
3 Responses to “MySQL does not support variables with LIMIT in stored procedures”
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 $$
By Federico on Jul 17, 2007
Sorry but according the manual you cannot use prepare or execute instructions on a procedure or function
By proteo2000 on Jul 17, 2007
what can I say? give it a try, it is working for me on a MySQL 5.0.37 version server running on Windows
By Federico on Jul 18, 2007