Bug #6181 Cannot use a variable to set LIMIT in a stored procedure
Submitted: 20 Oct 2004 16:55 Modified: 20 Oct 2004 22:09
Reporter: Metin Hamidof Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.0 Alpha OS:Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[20 Oct 2004 16:55] Metin Hamidof
Description:
I cannot use a variable in a stored procedure to set the LIMIT on a SELECT STATEMENT:

I cannot create the following stored procedure:

CREATE PROCEDURE sp_test ()
BEGIN

DECLARE testvar INT;
SET testvar = 10;

SELECT * FROM audit
ORDER BY timestamp
LIMIT testvar;

END

I recieve the folllowing error:
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 'testvar;
END' at line 3

How to repeat:
CREATE PROCEDURE sp_test ()
BEGIN

DECLARE testvar INT;
SET testvar = 10;

SELECT * FROM audit
ORDER BY timestamp
LIMIT testvar;

END

Suggested fix:
??
[20 Oct 2004 20:20] Peter Gulutzan
The MySQL Reference Manual says: 
 
"LIMIT takes one or two numeric arguments, which must be integer constants." 
 
http://dev.mysql.com/doc/mysql/en/SELECT.html
[20 Oct 2004 22:09] MySQL Verification Team
How Peter G. has pointed before.