MySQL: passing limits as stored procedure parameters

Posted June 23, 2008 * Comments(1)

Unfortunately MySQL 5 (at least 5.0.15) does not allow using variables or procedure parameters with LIMIT.

Here is a workaround where SQL statement is generated inside stored procedure:

CREATE PROCEDURE GetList (limitStart INT, limitCount INT)
BEGIN
 
SET @SQL = CONCAT('SELECT ID, Name FROM `User`
LIMIT ', limitStart, ', ', limitCount);
 
PREPARE query FROM @SQL;
EXECURE query;
DEALLOCATE PREPARE query;
 
END;

1 Comment so far

  1. Aria Stewart (@aredridel) on April 20th, 2013

    This is no longer needed in MySQL 5.5.6 . You can just use a variable directly.

Leave a reply