Stored procedure, using variable in LIMIT expression
14,350
Solution 1
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.
Solution 2
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.
Author by
ოთო შავაძე
Updated on June 17, 2022Comments
-
ოთო შავაძე about 2 years
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?