Saturday, April 21, 2012

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

No comments:

ShareThis

Welcome

Welcome to Rajesh Prajapati, asp.net blog.
Here you can find some useful code and information about asp.net., c#, VB.net, SQL Server, Web Service, Web Designing etc