passing LIMIT as parameters to MySQL sproc
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?
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
.
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
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;
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;
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;;
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;
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 ;
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.
精彩评论