Using variable in a LIMIT clause in MySQL

MPX_24694 0
Viewed 74377

I am writing a stored procedure where I have an input parameter called my_size that is an INTEGER. I want to be able to use it in a LIMIT clause in a SELECT statement. Apparently this is not supported, is there a way to work around this?

# I want something like:
SELECT * FROM some_table LIMIT my_size;

# Instead of hardcoding a permanent limit:
SELECT * FROM some_table LIMIT 100;
clause limit mysql variables
9 Answers

STORED PROCEDURE

DELIMITER $
CREATE PROCEDURE get_users(page_from INT, page_size INT)
BEGIN
  SET @_page_from = page_from;
  SET @_page_size = page_size;
  PREPARE stmt FROM "select u.user_id, u.firstname, u.lastname from users u limit ?, ?;";
  EXECUTE stmt USING @_page_from, @_page_size;
  DEALLOCATE PREPARE stmt;
END$

DELIMITER ;

USAGE

In the following example it retrieves 10 records each time by providing start as 1 and 11. 1 and 11 could be your page number received as GET/POST parameter from pagination.

call get_users(1, 10);
call get_users(11, 10);
user_5292180

A search turned up this article. I've pasted the relevant text below.

Here's a forum post showing an example of prepared statements letting you assign a variable value to the limit clause:

http://forums.mysql.com/read.php?98,126379,133966#msg-133966

However, I think this bug should get some attention because I can't imagine that prepared statements within a procedure will allow for any procedure-compile-time optimizations. I have a feeling that prepared statements are compiled and executed at the runtime of the procedure, which probaby has a negative impact on efficiency. If the limit clause could accept normal procedure variables (say, a procedure argument), then the database could still perform compile-time optimizations on the rest of the query, within the procedure. This would likely yield faster execution of the procedure. I'm no expert though.

TheSoftwareJedi_189410

I've faced the same problem using MySql 5.0 and wrote a procedure with the help of @ENargit's answer:

CREATE PROCEDURE SOME_PROCEDURE_NAME(IN _length INT, IN _start INT)
BEGIN
    SET _start = (SELECT COALESCE(_start, 0));
    SET _length = (SELECT COALESCE(_length, 999999)); -- USING ~0 GIVES OUT OF RANGE ERROR
    SET @row_num_personalized_variable = 0;

    SELECT
    *,
    @row_num_personalized_variable AS records_total         
    FROM(
        SELECT
        *,
        (@row_num_personalized_variable := @row_num_personalized_variable + 1) AS row_num
        FROM some_table
    ) tb
    WHERE row_num > _start AND row_num <= (_start + _length);
END;

Also included the total rows obtained by the query with records_total.

user_101024940

you must DECLARE a variable and after that set it. then the LIMIt will work and put it in a StoredProcedure not sure if it works in normal query

like this:

DECLARE rowsNr INT DEFAULT 0;  
SET rowsNr = 15;  
SELECT * FROM Table WHERE ... LIMIT rowsNr;
user_12499450
Related