Advertisements
Home > MySQL > Pagination Stored Procedure in MySQL

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 ;
Advertisements
Categories: MySQL Tags:
  1. martial
    April 4, 2012 at 8:38 am

    Thanks a lot for this procedure.

  2. April 4, 2012 at 5:26 pm

    Hi martial,
    Thank you for your comment.
    Please keep visiting blog 🙂

  3. Dave
    August 30, 2012 at 12:14 am

    This is really helpful. This is the paging technique I have been looking for. This the way to get high performance data retrieval from mysql to web front….

    Many thanks for sharing….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: