SQL Custom Paging

Using SQL Server Stored Procedure for implementing Custom Paging:

All of us would have implemented Paging in our applications.


Paging is particularly useful if you have lots of records to be displayed on a page and you can't get them displayed in one stretch. Say we have 1000 records to be displayed in a page. In this scenario, we cannot show up all the records in a single stretch in the page. Hence we need to implement Paging functionality whereby users can see a set of records and then click on a Button/Link to view the next set of records.

Here is the sample store procedure that is used for the SQL Server store procedure for paging.

Example:

CREATE PROCEDURE USP_PRODUCT_SELECT
(
@CurrentIndex int,
@TotalPageSize int
)
 AS

SELECT * FROM
(

SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS RowId, * FROM Products

) AS  P
WHERE  P.RowId BETWEEN (@CurrentIndex -1) * @TotalPageSize+ 1 AND (@CurrentIndex  * @TotalPageSize )

GO

#SQL Server, #Custom Paging