CODEWITHSUNDEEP
mysql
Sheraz
Sheraz

Reputation: 276

Trying to use limit with variable in MySQL

I am trying this code to use LIMIT in MYSQL with a variable. When I use a simple number for LIMIT it works fine but when I use a variable then it's not working.

SET @increment:=9;
SET @number:=1;
SET @end:=10*@number;
SET @start:=@end-@increment;

SELECT name, detail FROM tab1 LIMIT @start, @end;

When I use the below code (no variable) it works fine:

SELECT name, detail FROM tab1 LIMIT 0,10;

enter image description here

Upvotes: 5

Views: 1678

Answers (2)

Gurkan Yesilyurt
Gurkan Yesilyurt

Reputation: 2675

I'm using in this way. Maybe it'll help you.

SET @increment := 4;
SET @number := 1;
SET @end := 5 * @number;
SET @start := @end - @increment;


PREPARE stmt FROM 'SELECT @i := total, user FROM logintable LIMIT ?,?';
EXECUTE stmt USING @start, @end;

SELECT @i AS result;

Upvotes: 4

Phil Cooper
Phil Cooper

Reputation: 3123

The values need to be constant.

According to the spec:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants.

https://dev.mysql.com/doc/refman/5.5/en/select.html

Upvotes: 0

Related Questions