How to make limit offset dynamic using only (My)SQL

Question

This code doesn't work

select pagenr into @offset from pages where id = 3;
select * from table1 limit @offset*10, 10;

What SQLcode do I need to use in order to get this kind of code to work
using only SQL!

Note that

SET SQL_SELECT_LIMIT = @count 

doesn't work because I'm mainly concerned with the offset, not the limit as such.

Solution

From the MySQL 5.5 specification:

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, with these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

So, inside a stored procedure, the following would work:

DECLARE offset bigint
SELECT pagenr * 10 INTO offset FROM pages where id = 3;
SELECT * FROM table1 LIMIT offset, 10;

Otherwise, you'll need to precompute the value and pass it in via the query. You should already know the page size and page number, so this shouldn't be difficult.

Comments

[bobflux]

Aren't @variable session variables ?

[Johan]

@peufeu, @variables yes, but Kazelzarath is not using @variables.

[Karelzarath]

I did edit the example to be more complete. Initially, I was just going off what he's already posted.