Using variable in a LIMIT clause in MySQL

Question

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; 

Answer 1

For those, who cannot use MySQL 5.5.6+ and don't want to write a stored procedure, there is another variant. We can add where clause on a subselect with ROWNUM.

SET @limit = 10; SELECT * FROM (   SELECT instances.*,           @rownum := @rownum + 1 AS rank     FROM instances,           (SELECT @rownum := 0) r ) d WHERE rank < @limit; 

Answer 2

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

call get_users(1, 10);