Looping through limit in mysql stored procedure

Question

I am having a table containing details in MySQL. here I am calling this stored procedure function to js file in my project. if I open web app containing messages section display no. of messages from 1 to 15 only and when I click on next the no. of messages are from 16 to 30 likewise. NOTE: I wish to write a mathematical expression or loop through that limit function in the stored procedure. how should I approach to those concepts?enter image description here

question from:https://stackoverflow.com/questions/65880965/looping-through-limit-in-mysql-stored-procedure

Answer 1

Use prepared statement and provide x and y to it as parameters.

Example.

-- create procedure with dynamic SQL
CREATE PROCEDURE select_with_dynamic_limit (xlimit INT, xoffset INT)
BEGIN
    SET @limit := xlimit;
    SET @offset := xoffset;
    SET @sql:='SELECT id FROM test ORDER BY id LIMIT ?, ?';
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @limit, @offset;
    DROP PREPARE stmt;
END

-- Call procedure specifying needed limit/offset
CALL select_with_dynamic_limit(2,2);
CALL select_with_dynamic_limit(5,10);

DEMO