Stored Procedure Using a Variable in a LIMIT Expression

Question

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

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

but if I use the variable as a seed in a 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 a LIMIT expression inside a stored procedure?

Answer 1

You cannot use a variable directly. The good workaround 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 - http://bugs.mysql.com/bug.php?id=8094 .

You can also read more about the prepared instructions in the manual .

Answer 2

You can do this in MySQL 5.5 - the SELECT statement .

From the documentation:

Inside stored programs, LIMIT parameters can be specified using integer values ​​of standard parameters or local variables in MySQL 5.5.6.