Re: Using a variable with LIMIT in SELECT statement

[ddelanuez]

I have found a work-around inspired by http://dev.mysql.com/doc/refman/5.0/en/select.html.

For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following statements will return one row from the tbl table:

mysql> SET @a=1;
mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";
mysql> EXECUTE STMT USING @a;

So to do a stored procedure this way, with "<tablename>" being the name of your table works nicely:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLimit` $$
CREATE PROCEDURE `testLimit` (IN testLim INT)
BEGIN
SET @TL = testLim;
PREPARE STMT FROM "SELECT * FROM <tablename> LIMIT ?";
EXECUTE STMT USING @TL;
END $$

DELIMITER ;


I tried it using the SP's argument directly but that didn't work, so the session or user variable appears to be necessary.