SQL SERVER Paging - Method 1

 

Pagination is the technique to display data when there is more data than it is possible to put on one screen. Paging can either be done at the “Application level” or at the “Database level”.In the past, developers would have to design stored procedures to accomplish paging using temp tables, or would have to return the entire result set back to the client and page the data set in that fashion.

Here 2 simple methods are explained which can accomplish paging at the Database level.

Method 1
------------

create procedure Paging_Sp1
@RecsPerPage int,
@Page int
as
begin
Select TOP (@RecsPerPage) * FROM Products P WHERE p.Productid NOT IN
(select top (@RecsPerPage*(@Page-1)) p1.Productid FROM Products P1 ORDER BY p1.Productid) order by p.Productid
end

To view the ResultSet
---------------------------

exec Paging_Sp1 15,3

Please see the below image to have a better idea. Click on the image to view it bigger.

Description
--------------

This Stored procedure "Paging_Sp1" accepts two parameters @RecsPerPage (No of records per page) and @Page (Page number).

In this the subquery/inner query retrieves "(@RecsPerPage*(@Page-1))" rows and the main query strips out that much rows to get the result.In this example a primary/unique key like "Productid" is required in the table for accomplishing the task.

Note: This procedure is using TOP keyword (2005 feature) for retrieving the specified "@RecsPerPage" records from the table Products.TOP keyword was very much available in the previous versions of SQL Server itself,but now in 2005 “TOP” keyword accepts parameters.

ResultSet
------------

 

SQL SERVER Paging - Method 2