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

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;

Answer 2

Prior to 5.5.6, LIMIT could not be parameterized in MySQL stored procedures. You'd need to build the query dynamically and execute it.

In 5.5.6 and above, you can just pass the stored procs parameters as arguments to LIMIT and OFFSET as long as they are INTEGER.

Answer 3

I just found a solution which may be helpful. Use declared variables in your stored procedure and set them to your parameters

eg.

 CREATE PROCEDURE MyProcedure(
   IN paramFrom INT,
   IN paramTo INT
  )
   BEGIN
       DECLARE valFrom INT;
       DECLARE valTo   INT;

       SET valFrom = paramFrom;
       SET valTo = paramTo;

       SELECT * FROM myTable LIMIT valFrom, valTo;
    END

Answer 4

Simple solution

CREATE PROCEDURE `some_proc` (
IN _START INTEGER, 
IN _LIMIT INTEGER 
)
BEGIN 
PREPARE STMT FROM 
" SELECT * FROM products LIMIT ?,? "; 
SET @START = _START; 
SET @LIMIT = _LIMIT; 
EXECUTE STMT USING @START, @LIMIT;
DEALLOCATE PREPARE STMT;
END $$ 

Try prepare statement in stored procedure.

Answer 5

The following worked just fine in MySQL 5.5.35. It also worked in another procedure where the same SELECT was used within a DECLARE . . . CURSOR statement.

CREATE PROCEDURE `test`(
  IN `lim_val` INT,
  IN `lim_offset` INT
)
BEGIN
  SELECT array_ident_id
    FROM ArrayIdents
    ORDER BY array_ident_id
    LIMIT lim_val OFFSET lim_offset;
END;

Answer 6

pagination without statements:

create PROCEDURE test(
  IN first_rec integer,
  IN rec_count integer
)
BEGIN
  -- return --
  SET @rownum=0;
  SELECT * FROM (
    SELECT
    user.*, @rownum:=@rownum+1 AS rn FROM user
  ) t WHERE rn>=first_rec and rn<first_rec+rec_count;
END;;