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;
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;
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);
I know this answer has come late, but try SQL_SELECT_LIMIT.
Example:
Declare rowCount int;
Set rowCount = 100;
Set SQL_SELECT_LIMIT = rowCount;
Select blah blah
Set SQL_SELECT_LIMIT = Default;
SELECT ... part of queries such as REPLACE ... SELECT ....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.
This feature has been added to MySQL 5.5.6. Check this link out.
I've upgraded to MySQL 5.5 just for this feature and works great. 5.5 also has a lot of performance upgrades in place and I totally recommend it.
Another way, the same as wrote "Pradeep Sanjaya", but using CONCAT:
CREATE PROCEDURE `some_func`(startIndex INT, countNum INT)
READS SQL DATA
COMMENT 'example'
BEGIN
SET @asd = CONCAT('SELECT `id` FROM `table` LIMIT ',startIndex,',',countNum);
PREPARE zxc FROM @asd;
EXECUTE zxc;
END;
PREPARE and EXECUTE statement. One important note : we don't need to use Create procedure. We can use PREPARE**and **EXECUTE statement in major code sql too.As of MySQL version 5.5.6, you can specify LIMIT and OFFSET with variables / parameters.
For reference, see the 5.5 Manual, the 5.6 Manual and @Quassnoi's answer
It seems that many people here want to use LIMIT as a parameter without stored procedures.
it can be done with PREPARE and EXECUTE like this:
set @my_size = 100;
set @sql = concat("SELECT * FROM some_table LIMIT ", @my_size);
PREPARE stmt FROM @sql;
EXECUTE stmt;
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.
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;
LIMIT @nrows OFFSET @noffsetin a stored routine seems to be rejected too, while aLIMIT nrows OFFSET noffsetis accepted, as long as youDECLARE nrows INT; DECLARE noffset INTat the routine's begining.