Pagination Stored Procedure in MySQL

Simple steps to create pagination store procedure in MySQL

We have already posted how to do pagination in sql server in last post. But in the case of MySQL as database we need to create procedure some little bit different from SQL. MySQL provides a technique to fetch some small amount of data by giving starting row and end row using keyword LIMIT.

How to select paging data from MySQL using Limit Keyword

Limit keyword is used to fetch paging data from mySQL. We can send parameter as firstRow and lastRow that we need to fetch from the table.

Eg : Select * from items Limit 0,10   >> return firt 10 records
     Select * from items Limit 11,10   >> return 10 records starts from 11

But the the problem is that in stored procedure if we send fromRow and lastRow as parameter, we are unable to set this parameter with Limit keyword, because this facility is not supported by mySQL.

ie:  Select * from items Limit 0,10 >> it will work
     Select * from items Limit firstRow,lastRow >> It will not work

Dynamic query in pagination stored procedure in mySQL

For resolving the issue, we need to create dynamic stored procedure and execute the statement. Dynamic query is nothing but we are dynamically creating query, means set a query to variable and run this variable using EXECUTE keyword.

Following Pagination stored procedure accept firstRow and lastRow parameter and create a dynamic query and executed. It will send only the records between firtRow and lastRow. In order to maintain the pagination in development we need to get total number of records, here it is achieving by the query select found_rows().

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `myDB`.`usp_getItems` $$
CREATE PROCEDURE ` myDB `.`usp_getItems` (
firstrow int,
lastrow int
)
BEGIN

set @sql = concat(
'SELECT items.ID,items.Name,items.Description,items.Overview,
items.Price,items.Image,items.HasOffer,'
, 'items.OfferPrice,items.OfferDescription,
items.ItemCode,category.ID as CategoryID,'
, ' category.Name as category,subcategory.ID as SubCategoryID, '
, 'subcategory.Name as subcategory, items.IsActive, 
items.ReleaseDate, items.Specefications '
, 'FROM items INNER JOIN subcategory ON '
, 'items.SubCategoryID = subcategory.ID 
INNER JOIN category ON subcategory.CategoryID = category.ID '
, ' ORDER BY Name LIMIT '
, firstrow , ',' , lastrow
);

/*select @sql; */

PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

select found_rows();

END $$

DELIMITER ;