D365 F&O Solutions Asp.Net SQL Custom paging in SQL Server

Custom paging in SQL Server

Description:-

in this article we will see about how to create custom paging using sql store procedure and get in dot net. here i have use store procedure to create custom paging in dot net using store procedure.

Store Procedure:-

CREATE PROCEDURE [ProcedureName]
(
    @CurrentIndex int,
    @PageSize int
)
AS
SELECT * FROM(
          SELECT ROW_NUMBER() over(order by ProductID) AS RowId,
                     Product_Name
          FROM    Products
    )a 
WHERE RowId BETWEEN (@CurrentIndex -1) * @TotalPageSize+ 1 
                      AND     (@CurrentIndex  * @TotalPageSize )