SQL SERVER Paging - Method 2

 

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 2
-----------

create procedure Paging_Sp2
@RecsPerPage int,
@Page int
as
begin
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Productid) AS Rownum, * FROM Products) AS Products_T
WHERE Rownum >= @RecsPerPage*(@Page-1) AND Rownum <= @RecsPerPage*(@Page)
end

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

exec Paging_Sp2 15,3

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

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

This procedure is using a new sql 2005 function "ROW_NUMBER()",for generating row numbers. Here the "rownum" and all the fields of the products table are initially pushed in to a derived table "Products_T" (A derived table is one that is created on-the-fly using the SELECT statement, and referenced just like a regular table or view ).The Products_T table is used for further data retrieval by checking the row number.

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

 

SQL SERVER Paging - Method 1

Author:	Aneesh Kuzhikattil
	He is an accomplished IT professional with over 4 years of extensive experience in Microsoft technologies.