passing LIMIT as parameters to MySQL sproc

Question

I'm creating a paging class and need to pass in two parameters to my MySQL stored procedure for the LIMIT clause.

I'm passing them in as INTs and trying something like this

SELECT *
FROM
`MyTable`
LIMIT
MyFirstParamInt, MySecondParamInt

it gives me an error when I try and save the sproc though. Is there a way to do this that I'm just missing? Or am I going to have to EVAL the whole query and EXECUTE it?

Answer 1

LIMIT cannot be parametrized in MySQL stored procedures.

You'll need to build the query dynamically and execute it.

Answer 2

From http://dev.mysql.com/doc/refman/5.1/en/select.html:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

Here's prepared statement example which might help you:

SET @skip=1;
SET @rows=5;

PREPARE STMT FROM 'SELECT * FROM table LIMIT ?, ?';
EXECUTE STMT USING @skip, @rows;