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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.0 Alpha
• OS: Windows (Windows 2000)
• 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.