Bug #11918 SP does not accept variables in LIMIT clause
Submitted: 13 Jul 2005 17:09 Modified: 24 Sep 2010 19:59
Reporter: Markus Popp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.0, 5.1 OS:Any
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: limit, Parameters, PS, SP, variables
Triage: Triaged: D5 (Feature request)

[13 Jul 2005 17:09] Markus Popp
Description:
Due to Per-Erik Martin's recommendation in the forum at http://forums.mysql.com/read.php?98,28212 I'm filing the following behavior of SP as bug report.

SPs don't accept variables in the LIMIT clause. If such a SP is created, the error message

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'l1, l2;

is thrown.

How to repeat:
Create following table with following data:

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL auto_increment,
  `ct` char(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

INSERT INTO `t1` VALUES (1, 'a');
INSERT INTO `t1` VALUES (2, 'b');
INSERT INTO `t1` VALUES (3, 'c');
INSERT INTO `t1` VALUES (4, 'd');
INSERT INTO `t1` VALUES (5, 'e');
INSERT INTO `t1` VALUES (6, 'f');
INSERT INTO `t1` VALUES (7, 'g');
INSERT INTO `t1` VALUES (8, 'h');

Creating this SP works fine:

DELIMITER //

DROP PROCEDURE IF EXISTS limittest //
CREATE PROCEDURE limittest (IN l1 INT, IN l2 INT)
BEGIN
select l1;
select l2;
select * from t1 order by id;
END //

DELIMITER ;

If you add these two variables in the LIMIT clause, you get an error:

mysql> DELIMITER //
mysql>
mysql> DROP PROCEDURE IF EXISTS limittest //
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE PROCEDURE limittest (IN l1 INT, IN l2 INT)
    -> BEGIN
    -> select l1;
    -> select l2;
    -> select * from t1 order by id limit l1, l2;
    -> END //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'l1, l
2;
[13 Jul 2005 17:21] Paul Dubois
This isn't specific to stored routines.
LIMIT doesn't allow variables in any context.
Its arguments must be integer constants.
[13 Jul 2005 17:30] Markus Popp
However, I think this would be a useful feature (e.g. for paging in websites).
[13 Jul 2005 17:49] Miguel Solorzano
Like noticed by Paul. It isn't a bug but you would change it for feature request.
[13 Jul 2005 17:57] Markus Popp
> but you would change it

Do I have to take any action to make a feature request out of it?
[13 Jul 2005 18:05] Miguel Solorzano
I changed it, now just to wait if that is possible from dev.

Thank you for the report.
[14 Jul 2005 9:55] Per-Erik Martin
It is a feature request. It is SP specifik in the sense that it requires a special fix for SP variables. (It has already been fixed for '?' in prepared statements.)
If it's fixed for stored procedure variables, it should also be considered for user variables as well.
[13 Dec 2005 21:54] Gabriel Harriman
Has this been added yet?
[26 Dec 2005 20:49] Ran Biron
I've found a workaround for this problem. 
It's ugly, time consuming and... mainly ugly. But it's working. 

------SQL-------- 
DELIMITER $ 
CREATE PROCEDURE `tmp`() 
BEGIN 
PREPARE STMT FROM "SELECT * FROM users LIMIT ?,?"; 
END$ 
DELIMITER; 

SET @a=2; 
SET @b=1; 

CALL tmp(); 
EXECUTE STMT USING @a, @b; 
------SQL-------- 

It's tested to be working on 5.0.15. 
Hope if helps anyone.
[8 Feb 2006 11:20] Konstantin Osipov
Suggested workaround: use Dynamic SQL in stored procedures.
User variables are accepted arguments of LIMIT clause in prepared statements, and SQL syntax for prepared statements can be used in stored procedures.
[8 Feb 2006 12:38] Markus Popp
I know about this workaround, but still I would believe that directly assigning the parameters in the LIMIT clause would be much more convenient for users.
[3 May 2006 10:20] Dhruba Bandopadhyay
Hi.  Any  progress on this?  I also feel this would be an extremely useful feature.  To be honest I was quite shocked that one could pass in an order by column name via an input param but not a limit or offset value.  I expected this to be already present.  I don't think it is unreasonable request.  It would be an intuitive feature and immensely useful for pagination.  The lack of this feature may mean I have to forsake using SPs for my data layer which is a huge sacrifice.  Any thoughts?
[3 Aug 2006 4:16] Rajesh Balu
Has this been fixed or anywhere close? All data access in my web application are done using stored procedures and without being able to use variables in LIMIT clause means we simply cant implement pagination. This would really be a very useful feature.
[28 Aug 2006 14:23] Nick dAlterio
I disagree that this is a feature request. It is bug that results in a serious limitation on functionality and means that paging is not possible when using stored procedures.

Is there any version where this bug has been fixed 5.1.x ?
[12 Nov 2006 20:44] Erhhung Yuan
I've found a workaround to this that isn't too ugly:

create procedure foo ( limit_val int )
begin
  set @sql = concat('select * from myTable limit ', limit_val);
  prepare stmt from @sql; execute stmt;
end
//

I'm using MySQL 5.0.24. Apparently, you can't use a declared VARCHAR variable for the concatenated SQL string, only a user variable.
[14 Feb 2007 8:15] Gert Brigsted
Any news on this one? I'm not resorting to using prepared statements. The main reason for this is that if I use prepared statements, I feel that I just as well could use texted SQL. Which I'm never going to do again! :)
[15 Feb 2007 8:40] Jeremy Wyman
Using a prepared statement is that bad of a work around. If you're worried about security, use the limit as the only variable and select from a temp table that you've created with in the sproc.
[19 Feb 2007 14:58] Gert Brigsted
I just made up my own workaround, without using any texted sql. I have also done som performance testing, and it seems to be doing fine... :)

DELIMITER $$

DROP PROCEDURE IF EXISTS `[database]`.`[table]List` $$
CREATE PROCEDURE `[database]`.`[table]List` (_limit int, _offset int)
BEGIN
set @rownum:=0;

select * from(
 select
   (@rownum:=@rownum+1) as Rownumber,
   [table].*
 from
  [table]
 order by
   id desc
) as t
where
 Rownumber > _offset AND
 Rownumber <= (_offset + _limit);
END $$

DELIMITER ;

Just replace [database] with your database, and [table] with the table you want. And you might want to modify the name of the stored procedure, too... :)

If you need to change anything (sorting, selection or whatever) do it in the NESTED STATEMENT, not the outer statement, or it may not work!
[5 Apr 2007 2:51] Nick S
Why has this not been fixed yet?
It's a shame that this bug has existed for so long, with no updates.
[15 Jun 2007 9:44] Rikard von Zweigbergk
I think this is a very important feature. Instead of using paging i your application, yo can do it in th database layer (where it belongs).

Currently I'm working on a sp that should return one row, show it to the user och ask if he/she want to se next row.
But since this feature isen't added yet, I have to send all rows to the application and sort it out there.

as you probobly understand this isn't very efficient at all.

this feuture enables paging to be run at the database layer where it belongs.

Regards,
Rikard von Zweigbergk
DBA
[5 Jul 2007 11:20] Martin Sillence
A nicer workaround from Kian Ryan:

DROP PROCEDURE IF EXISTS get_product_range $$
CREATE PROCEDURE get_product_range (
IN _START INTEGER,
IN _LIMIT INTEGER
)
BEGIN
PREPARE STMT FROM
" SELECT *
FROM products LIMIT ?,? ";
SET @START = _START;
SET @LIMIT = _LIMIT;
EXECUTE STMT USING @START, @LIMIT; /* Known bug in mySQL5 - exists as feature req. */
END $$
[31 Jul 2007 15:40] Aleksandar Vojnovic
Why is this not fixed yet?!?
[19 Dec 2007 22:43] Slawek Kaczor
Any target date to fix this and make our lifes easier and better:)?
[21 Jan 2008 6:13] St├ęphane GRAZIANO
I personnaly found a fix here : http://bugs.mysql.com/bug.php?id=33856 . But this fix is incomplete (see the comment)
[12 Feb 2008 20:06] Aleksandar Vojnovic
Just to bump up the timestamp of this bug :) (and probably get some attention from the dev team and then TAAAA TA RA TA TAAAA ... and the issue is fixed )
[9 Jul 2008 2:55] Ilkka Huotari
I started to use stored procedures today for the first time and was shocked about this limitation/bug.

Jesus, get it fixed already please.
[20 Jul 2008 14:40] kyle nathan
Fix please ASAP guys. This is a severely limiting lack of feature/bug that has been requested for over 3 years now, and, correct me if I'm wrong, shouldn't too hard to implement.
[31 Aug 2008 23:19] Dan Makovec
Still an issue in 5.1.26-rc
[30 Sep 2008 16:57] Ian O'Shaughnessy
This really needs to be fixed. Please fix this.
[12 Oct 2008 20:28] Sirish Mahavadi
any update on this bug/feature?  I am using 5.1.28....
[13 Oct 2008 11:57] Aleksandar Vojnovic
Just a reminder to developers that this issue still persist.
[9 Dec 2008 12:11] Lau Guerreiro
COME ON!!! What the F#@% is going on here?
This ESSENTIAL feature has been missing for over 3 years now!! THREE YEARS!!

One would have to assume that there have been many less essential features that have been added during that time. And that means that somebody in a senior position has deliberately decided to withhold this essential functionality.

Why would somebody do that! Does somebody have a vendetta against stored procedures?

This is not a feature request this is essential functionality. (I suppose that if the IF THEN END IF statement was missing you would call that a feature request too would you?)

Come on!! This is beyond ridiculous. How do we get this escalated to someone at a more senior level that can finally get this thing done.

Is somebody on staff reading this? If there is someone on staff reading this please respond to at least let us know that you are aware of this problem ... and hopefully explaining why this hasn't been done yet and when you expect to get it done.
[9 Dec 2008 13:52] Gert Brigsted
This bug (and the complete lack of support for LINQ/Entity Framework) has made me change to SQL Express. That's what you get for neglecting your users.
[9 Jan 2009 15:26] Frederic Steinfels
can't believe this bug is not getting fixed. adding the functionality to return arrays from selects in stored procedure could be called a feature request. the lack of this function is rather a bug than a feature because no one would assume variables would not work in the limit clause when they work everywhere else in the statement.
[19 Feb 2009 22:07] Jo Yoshida
If anyone on the development team reads this thread, could you please let us know if a fix is being considered or not for an upcoming release? This is a killer feature, glaring in its omission, and for it to have been ignored for nearly four (4) years this summer is truly a kick in the groin.

C'mon, make us happy! Thanks! :)
[26 Feb 2009 22:07] Jonathan Shaltz
Here's one more user giving two cents.  This missing feature is an embarrassment to MySQL; it needs to be added.  I try to support open source software with my clients, but when they have technical staff I feel like I have to put a paper bag over my code to hide ugly workarounds like this.  Please make it happen.
[18 Mar 2009 15:18] Sean Cullinan
While I agree that this is annoying, we have just been informed that there is a much better approach then what I've seen so far.  Rather than put the limit clause in your SQL simply do the following:

set SQL_SELECT_LIMIT = p_LimitNum;

Issue your select but with no limit clause

set SQL_SELECT_LIMIT = default;

In our testing this works great and the only modifications you have to do to your queries is to remove the limit clause entirely since we are setting it at the top.

Sean
[18 Mar 2009 17:15] Jo Yoshida
Sean, I've been using the nested select solution as per Gert Brigsted (19 Feb 2007). It works well.

If the inner select statement would return 50 records but my web site is displaying 10 records per grid page, is it possible to use SQL_SELECT_LIMIT to page through the sequential sets of 10 records without using a nested select construct? Sorry for my laziness but I have a SQL Server background and I can't see a way to do this off the top of my head. Thanks.
[11 Aug 2009 14:49] Lawrence Holtsclaw
I just ran into this problem myself last night and wasted 3 hours trying to find a workaround that did not involve user variables and preparing the statement first. The diagnostics generated are reminiscent of the late 70's (syntax error).

1. The LIMIT clause should accept procedure variables.
2. The PREPARE statement should accept a procedure variable.
3. The EXECUTE statement should accept procedure variable(s).

THERE IS NO REASON THIS SHOULD BE CALLED A "FEATURE REQUEST"

MySQL/Sun should be ashamed for letting such a severe problem languish here for OVER FOUR YEARS!!!! C'mon guys, this is ludicrous. Do your damn job and fix this.
[13 Aug 2009 9:00] Giuseppe Maxia
I am reopening this bug as a non-feature request.
The SP protocol should allow any statement that is possible in the prepared statement protocol. Since prepared statements allow LIMIT parameters, so should stored procedures. Hence, it's a bug.

How to repeat:

set @q = 'select * from t1 limit ?,?';
set @l1=10, @l2=1;
prepare q from @q;
execute q using @l1,@l2;
# succeeds 

create procedure p1 (l1 int,l2 int) deterministic  select * from t1 limit l1, l2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'l1,l2' at line 1
[13 Aug 2009 13:23] Peter Gulutzan
Please read the MySQL Reference Manual:

"
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).
"

http://dev.mysql.com/doc/refman/5.1/en/select.html
[13 Aug 2009 13:36] Lawrence Holtsclaw
So? All that means is the manual needs to be updated as well.
[13 Aug 2009 14:25] Konstantin Osipov
Patches are welcome here. It is not a difficult change -- a change in the grammar should be almost sufficient.
[14 Aug 2009 15:52] Omer Barnir
Setting back to Feature request. This is still a feature request and should be handled/escalated as such. Setting it to a bug is wrong.
[17 Aug 2009 17:38] Alan Givati
I was quite surprised to discover LIMIT takes only constants.  Thought I was doing something wrong.  Hope this gets resolved soon.  The workaround I am using in the meantime is to use prepared statements in my stored procedures.
[17 Aug 2009 19:26] henky schneida
This need so much to be fixed can't believe it isn't already...
[21 Aug 2009 17:32] Edgar Aguilar
This is a NEEDED feature for web apps, specifically for web paging.

Haven't find a good workaround for this "issue/feature request".

The Limit clause its so cool for paging, but this make it useless in Stored Procedures.

I don't f* care if its a bug or a feature, we need it..

By the way, I'm stil trying to do a workaround. I see that PREPARE statements inside Stored Procedures could be useful, and there can be edited with the session variables.... but.. ALL EXAMPLES I have found the query returns only a row...

How can I get a Result Set and iterate it INSIDE a MySQL's Stored Procedure???? i.e. How can I loop over multiple rows for a Prepare Statement??

Please post here any code sniped or send it to me edgarsalvadoraguilar at yahoo dot com

Many thx in advance..

P.D.
Sadly considering migrate to PostgreSQL
[29 Sep 2009 15:02] Matt Herndon
I ran into this issue today and I feel I must echo the desires of the other commenters that this should be implemented ASAP.  After perusing the current bugs for mysql I can see why this has been categorized as a feature request, and I can also see why it has languished for 5 years.  I'm new to mysql, but I have to say that it is disheartening to see an issue like this, which seems very important, buried under 3,000 other bugs which for the most part seem even worse.  Like I said I'm new to mysql and open source in general, but if there was a way I could get into the source code and implement this one feature I'd jump on it.  Outside of that happening I don't see much chance of this getting done.

Other notes:

Why has the thread discussing this issue been closed to comments?  Shouldn't there at least be a statement on that thread instructing commenters to transfer all discussion to this bug report?
http://forums.mysql.com/read.php?98,28212,260408#REPLY

SQL_SELECT_LIMIT won't help you if you're looking to use 2 different limits.  Say I want to select a random sample of 40,000 rows from the top 75% of rows based on some ranking column (say price of transaction at a store) in a table whose size I don't know.

select cast(count(1)*.75 as unsigned) into @top75 from table;
select * from (select * from table order by rank_column limit @top75) a order by rand() limit 40000;
[29 Oct 2009 14:56] Mattias Aspholm
There's absolutely no excuses why this is not fixed after more than 4 years! It's fundamental basics of SQL-scripting and stored procedures! Stop ignoring the problem now!
[30 Oct 2009 20:45] Bob Bell
Ugggghhhh, 

Is this fixed ?  Not even a Target Version ??? If your not going to do it, just kill it then, or just another sign Sun purchase, and coming Oracle purchase is really bad for us usere ? "Feature Request" ??  Humorous.

Bob
[30 Nov 2009 20:35] Adrian Hondema
Any news on this?
[13 Jan 2010 20:26] taesik han
LIMIT statement STILL does not accept variable.
It seems that this BUG has been intentionally ignored by mySQL developers. Why ?
[20 Jan 2010 1:55] Razvan Marescu
I just created a SP that was supposed to use this feature, but it seems that hasn't been implemented (5.1.42-community-log).

Could we please get a status update on this request? Is there any chance to be implemented?

Thanks
[19 Feb 2010 17:43] Sergei Shirokov
I want this feature too.
[25 Feb 2010 9:03] Alexandr Danilov
Seems everybody need this feature (me too)
But, during it's not implemented we can use another ways.

In my case, i use stored functions and i can't use dynamic SQL.
But, it's possible to use subqueries like below

CREATE FUNCTION myfunction()
  RETURNS text CHARSET cp1251
BEGIN
  DECLARE i INT;
  DECLARE f_count,f_id,f_parent_id

  -- set f_count, f_parent_id by some logic...

  SET i = 1;
  WHILE i <= f_count DO
    SET @rownum = 0;
    SELECT id INTO f_id FROM (
      SELECT @rownum=@rownum+1 AS rownum,id FROM mytable WHERE parent_id = f_parent_id ORDER BY id
    )z WHERE rownum = f_index;

    -- equal to SELECT id FROM mytable WHERE parent_id=f_parent_id LIMIT 0,f_index

    -- some logic

    -- next
    SET i = i + 1;
  END WHILE;

  -- ...
END
[15 Mar 2010 20:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103316

2993 Konstantin Osipov	2010-03-15
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      Pending tests, including tests of replication.
[13 Apr 2010 19:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105573

3013 Konstantin Osipov	2010-04-13
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      
      Allow stored procedure variables in LIMIT clause.
      Only allow variables of INTEGER types. 
      Handle negative values by means of an implicit cast to UNSIGNED 
      (similarly to prepared statement placeholders).
      Add tests.
      Make sure replication works by not doing NAME_CONST substitution
      for variables in LIMIT clause.
      Add replication tests.
     @ mysql-test/r/sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/t/rpl_sp.test
        Add a test case for Bug#11918.
     @ mysql-test/t/sp.test
        Add a test case for Bug#11918.
     @ sql/item.cc
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/item.h
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/share/errmsg-utf8.txt
        Add a new error message (a type mismatch for LIMIT
        clause parameter).
     @ sql/sp_head.cc
        Binlog rewrite sp variables in LIMIT clause without NAME_CONST
        substitution.
     @ sql/sql_string.cc
        Implement append_ulonglong method.
     @ sql/sql_string.h
        Declare append_ulonglong().
     @ sql/sql_yacc.yy
        Support stored procedure variables in LIMIT.
[13 Apr 2010 21:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105578

3013 Konstantin Osipov	2010-04-14
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      
      Allow stored procedure variables in LIMIT clause.
      Only allow variables of INTEGER types. 
      Handle negative values by means of an implicit cast to UNSIGNED 
      (similarly to prepared statement placeholders).
      Add tests.
      Make sure replication works by not doing NAME_CONST substitution
      for variables in LIMIT clause.
      Add replication tests.
     @ mysql-test/r/sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/t/rpl_sp.test
        Add a test case for Bug#11918.
     @ mysql-test/t/sp.test
        Add a test case for Bug#11918.
     @ sql/item.cc
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/item.h
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/share/errmsg-utf8.txt
        Add a new error message (a type mismatch for LIMIT
        clause parameter).
     @ sql/sp_head.cc
        Binlog rewrite sp variables in LIMIT clause without NAME_CONST
        substitution.
     @ sql/sql_string.cc
        Implement append_ulonglong method.
     @ sql/sql_string.h
        Declare append_ulonglong().
     @ sql/sql_yacc.yy
        Support stored procedure variables in LIMIT.
[13 Apr 2010 21:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105579

3015 Konstantin Osipov	2010-04-14
      A fix for Bug#11918 "SP does not accept variables in LIMIT clause"
      
      Allow stored procedure variables in LIMIT clause.
      Only allow variables of INTEGER types. 
      Handle negative values by means of an implicit cast to UNSIGNED 
      (similarly to prepared statement placeholders).
      Add tests.
      Make sure replication works by not doing NAME_CONST substitution
      for variables in LIMIT clause.
      Add replication tests.
     @ mysql-test/r/sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Update results (Bug#11918).
     @ mysql-test/suite/rpl/t/rpl_sp.test
        Add a test case for Bug#11918.
     @ mysql-test/t/sp.test
        Add a test case for Bug#11918.
     @ sql/item.cc
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/item.h
        Mark sp variables in LIMIT clause (a hack for replication).
     @ sql/share/errmsg-utf8.txt
        Add a new error message (a type mismatch for LIMIT
        clause parameter).
     @ sql/sp_head.cc
        Binlog rewrite sp variables in LIMIT clause without NAME_CONST
        substitution.
     @ sql/sql_string.cc
        Implement append_ulonglong method.
     @ sql/sql_string.h
        Declare append_ulonglong().
     @ sql/sql_yacc.yy
        Support stored procedure variables in LIMIT.
[27 Apr 2010 9:46] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)
[27 Apr 2010 9:49] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)
[27 Apr 2010 9:52] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)
[16 Sep 2010 6:03] Konstantin Osipov
This was queued without a code review and is present in 5.5.6.
Now you can use stored procedure variables in the LIMIT clause (but not user variables). I filed a separate bug for user variables in LIMIT, 
see Bug#56811.
[16 Sep 2010 6:14] Konstantin Osipov
Still have the code review comments to handle, see http://lists.mysql.com/commits/105585
[24 Sep 2010 19:59] Paul Dubois
Noted in 5.5.6, 6.0.14 changelogs.

Within stored programs, LIMIT clauses now accept integer-valued
routine parameters or local variables as parameters.
[27 Sep 2010 14:00] Konstantin Osipov
That's correct, Paul.
[30 Jun 2011 16:35] Matthew Ralston
Now what would be really useful (to me at least) is if this worked with cursors.

For example:

CREATE DEFINER=`matt`@`%` PROCEDURE `exampleLimit`()
proc:BEGIN

DECLARE iEof INT DEFAULT 0;
DECLARE iLimit INT;

DECLARE iExampleId INT;
DECLARE sName VARCHAR(255);

DECLARE cExamples CURSOR FOR
	SELECT exampleId, `name`
	FROM example
	LIMIT 0, iLimit;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET iEof=1;

-- TODO: Programatically determine the required limit
SET iLimit=10;

OPEN cExamples;
exampleLoop:LOOP
	SET iEof=0;
	FETCH cExamples INTO iExampleId, sName;
	IF iEof THEN
		LEAVE exampleLoop;
	END IF;

	-- TODO: Something useful...
	SELECT iExampleId, sName;

END LOOP exampleLoop;

END proc