Dynamic set limit error

[Verschnicker]

Hi
I am trying to create a procedure that does a lot of stuff, but the error comes in when i try to execute the select statement and dynamicaly set the limit. The following is the procedure which gives the error.

DELIMITER ^

DROP PROCEDURE IF EXISTS `serverName`.`p rocedureName`^

CREATE PROCEDURE `serverName`.`p rocedureName` (nLimit INT)
BEGIN

SELECT column FROM tableName
order by column LIMIT nLimit OFFSET 0;

END^

DELIMITER ;

When i try the following, it works:

DELIMITER ^

DROP PROCEDURE IF EXISTS `serverName`.`p rocedureName`^

CREATE PROCEDURE `serverName`.`p rocedureName` (nLimit INT)
BEGIN

SELECT column FROM tableName
order by column LIMIT 5 OFFSET 0;

END^

DELIMITER ;

Any idea why the first one does not work??

Thanks in advance

[Verschnicker]

Ok, it is not a bug, it's a limitation. The sollution or workaround is found here: http://dev.mysql.com/doc/refman/5.1/en/select.html - scroll to comment of June 24 2006.

[genoma77]

SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @numrows;

In this way you can solve your problem.
However if you go to
http://dev.mysql.com/doc/refman/5.1/en/select.html
you can try the same example and a good explanation.