Stored procedure, using variable in LIMIT expression

Question

I have a stored procedure in which if I write the following query without a variable, all: works well

 CREATE PROCEDURE `some_proc` ()
 BEGIN
 SELECT blabla FROM  mytable ORDER BY id LIMIT 3,1
 .....

but, if I use a variable as start number in LIMIT expression, I get an error:

 CREATE PROCEDURE `some_proc` ()
 BEGIN
 DECLARE start INT;
 SET start = 3;
 SELECT blabla FROM  mytable ORDER BY id LIMIT start,1
 .....

Is there a way to use a variable in the LIMIT expression inside the stored procedure?

possible duplicate of passing LIMIT as parameters to MySQL sproc — Pang
– Pang, Commented Mar 11, 2014 at 4:25

fancyPants · Accepted Answer · 2012-07-05 11:30:51Z

18

You cannot use a variable directly. A nice workaround that I've seen is -

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

Another search returned this - http://bugs.mysql.com/bug.php?id=8094.

Also you can read more about prepared statements in the manual.

edited Jul 5, 2012 at 11:30

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

answered Jul 5, 2012 at 11:26

FSP

4,8572 gold badges21 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2012-07-05 12:52:08Z

8

You can do it in MySQL 5.5 - SELECT statement.

From the documentation:

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

answered Jul 5, 2012 at 12:52

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

Stored procedure, using variable in LIMIT expression

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related