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

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 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

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 4

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 5

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;;

Answer 6

Best pagination example may help you

call user_list(v_private_key,v_user_id,v_pageIndex,v_limit,v_image_path, @o_rec_count, @o_error_code, @o_error_message)

DECLARE v_QueryLimit TEXT DEFAULT "";
DECLARE v_Select TEXT DEFAULT "";
DECLARE v_where TEXT DEFAULT '';
DECLARE v_From TEXT DEFAULT "";
DECLARE v_group_by TEXT DEFAULT " ";
DECLARE v_having TEXT DEFAULT "";
DECLARE v_OrderBy TEXT DEFAULT "";


SET o_error_code = '200';

SET v_Select = CONCAT(" SELECT
        AES_DECRYPT(email,'",v_private_key,"') AS email,
        AES_DECRYPT(first_name,'",v_private_key,"') AS first_name,
        AES_DECRYPT(last_name,'",v_private_key,"') AS last_name,
        AES_DECRYPT(mobile_no,'",v_private_key,"') AS mobile_no,
        CONCAT(AES_DECRYPT(first_name,'",v_private_key,"'),' ', AES_DECRYPT(last_name,'",v_private_key,"')) as full_name,
        CONCAT('",v_image_path,"','profile/',IFNULL(thumb,'user_thumb.png')) AS thumb,
        CONCAT('",v_image_path,"','profile/small/',IFNULL(thumb,'user_thumb.png')) AS thumb_small,
        IFNULL(country_code,'+91') as  country_code,
        IFNULL(unique_code,'') as user_code
    ");


SET v_From = CONCAT(" FROM userinfo WHERE role_group = 2  AND  id != ",v_user_id," ");

IF (v_PageIndex) > 0 THEN
    SET v_QueryLimit = CONCAT(" LIMIT ", v_limit, "," , v_pageIndex);
END IF;

-- set v_group_by = concat(' GROUP BY  ut.user_card_id,  ');        

SET @rec_Query= CONCAT(v_Select
            ,v_From
            ,v_Where
            ,v_group_by
            ,v_having
            ,v_OrderBy);

/**************** Get Record Count **************/
SET @cnt_Query = CONCAT("Select Count(*) INTO @o_rec_count FROM (",@rec_Query,") AS tmp");
PREPARE c2 FROM @cnt_Query;
EXECUTE c2;

SET o_rec_count=@o_rec_count;   

/**************** Calculate Limit **************/
IF (v_limit != "" && v_pageIndex != "")  AND @o_rec_count>0 THEN 
    CALL Calculate_Paging_Index(@o_rec_count ,v_limit,v_pageIndex,@new_start_limit);        
    SET v_QueryLimit = CONCAT(" LIMIT ",@new_start_limit, ",",v_limit);
END IF;

SET @vv2_Query= CONCAT(v_Select
            ,v_From
            ,v_Where
            ,v_group_by
            ,v_having
            ,v_OrderBy
            ,v_QueryLimit); 

PREPARE s2 FROM @vv2_Query;
EXECUTE s2;

SET o_error_message = "success";

calculate page index SP

CREATE PROCEDURE calculate_paging_index(in_count,in_limit,in_page,@out_start_limit)enter code here`

DECLARE count1 INT;
DECLARE total_pages INT;

SET count1  = in_count;
IF( count1 > 0 ) THEN 
    SET total_pages = CEIL(count1/in_limit);
ELSE 
    SET total_pages = 0;
END IF;

IF (in_page > total_pages) THEN 
SET in_page=total_pages;

END IF;
SET out_start_limit = in_limit * in_page - in_limit; 

Answer 7

DELIMITER &&  
CREATE PROCEDURE SP_ALL( pageno int, pagesize int)    
BEGIN    
declare pg int;
declare ps int;
set pg = ((pageno - 1) * PageSize);
set ps = (pageno * pagesize);
    SELECT * FROM Users where IsActive = true limit ps offset pg;     
END &&  
DELIMITER ; 

Answer 8

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.