Variable LIMIT statement in MySQL

Question:

Is there a way to use variables in conjunction with the SQL LIMIT clause?
(e.g. SELECT * FROM movies LIMIT @foo)?

Answer:

No not directly…at the time of writing this is still a big issue of MySQL Development. Nonetheless there exist various workarounds. I will discuss a few of them in this article.

Description:

The main issue is that you want to construct an SQL query that hands a variable as a parameter to the LIMIT clause. That could look something like this:

SET @foo = 5;
SELECT * FROM movies LIMIT @foo;

This immediately results in the following error message.

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@’ at line 1)

So to all appearances SELECT queries using the LIMIT clause do not work when a SQL variable (e.g. @foo) is used as a parameter in the LIMIT clause.

Solutions:

In the following I present a few workarounds to resolve this little handicap:

1. Make use of Prepared Statements

SET @foo=5;
PREPARE STMT FROM 'SELECT * FROM table LIMIT ?';
EXECUTE STMT USING @foo;

You could also concatenate your entire SELECT Statement and convert this to a Prepared Statement. In the following example the parameter “sParameter” is the used variable.

SET @myQuery = CONCAT('SELECT * FROM test ORDER BY ', sParameter);
PREPARE stmt FROM @myQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

If you use queries like these a lot, it might make sense to wrap the whole thing up in a stored procedure:

DELIMITER $$

CREATE PROCEDURE `get_movie_range`(
IN _FROM INTEGER,
IN _TO INTEGER
)
BEGIN
PREPARE STMT FROM "SELECT * FROM movies LIMIT ?,? ";
SET @FROM = _FROM;
SET @TO = _TO;
EXECUTE STMT USING @FROM, @TO; 
END$$

With the stored procedure in place you can make calls like:

CALL get_movie_range(7,(SELECT COUNT(*) FROM movies));

2. Make use of variables and the BETWEEN Statement

This statement selects all rows beginning with 12 and ending at 20.

set @i=0;
select * from movies where (@i:=@i+1) between 12 and 20;

So that’s it…hope it helps…:)