Mysql procedure to modify limit and offset

Question

Just a thought in my mind but would it not be possible to createa function in MySQL that can modify the LIMIT within a query

The clause would be a simple calculation function that would work out the offset depending on the pageno * perpage

Example

SELECT * FROM items PAGE(4,20)

this would be the same as

SELECT * FROM items LIMIT 100,20

I have never created any procedures before so the below would be wrong..

CREATE PROCEDURE (int pagno, int limit)
BEGIN
     ofsset = roundup(pageno * limit)
END

But within that query actually set the limit and offset for the query?

Answer 1

you can do something like this ofc:

drop procedure if exists foo;

delimiter #

create procedure foo
(
in p_limit int unsigned
)
proc_main:begin

set SQL_SELECT_LIMIT = p_limit;

select * from <table>...

set SQL_SELECT_LIMIT = DEFAULT; 

end proc_main #

delimiter ;
call foo(64);