Custom Paging

Custom Paging is one of the most efficient ways of improving performance in any application. When we use the paging that comes with in controls like Gridview, DataList, DetailsView, ListView etc... is not very efficient as the complete data gets loaded in the memory and paging takes place there. Custom Paging will help bring only request number of rows from our relational database.

In our example below we will be using Adventure Works Product table.

We would first need to get the total row count from table we are querying, as this would help us determine the number of pages in which result set will be divided. Say we have 995 total records in our table and we need to show 100 records/page, then whole result set will be displayed in 10 page, last page having 95 records.

So first we create our Count Procedure as below

create procedure dbo.Custom_Count

 

AS

begin

select COUNT(*) from SalesLT.Product

end

--Now we need to write the procedure that accepts 2 parameters, @startPageIndex for determining the starting point and @endPageIndex for accepting the number of records user wants view in one page.

alter PROCEDURE [dbo].[Custom_Paging]

    @startPageIndex int,

    @endPageIndex int

AS

BEGIN

    SET NOCOUNT ON;

Declare @sql as nvarchar(max)

 

select @sql='; with e as(

select ProductID,Name,ProductNumber,ROW_NUMBER() OVER(ORDER BY ProductID ASC) AS rm

from SalesLT.Product)

select ProductID,Name,ProductNumber,rm as Rownumber from e where cast(rm as int)>'+cast(@startPageIndex as varchar)

+' and cast(rm as int)<='+cast(@startPageIndex+@endPageIndex as varchar)

 

END

Exec sp_executesql @sql

If you want to implement this in your application you can view complete front end implementation here.

--Execute your above Procedure as below

 

EXEC    @return_value = [dbo].[Custom_Paging]

        @startPageIndex = 10,

        @endPageIndex = 10

 

SELECT    'Return Value' = @return_value

 

GO

Tags: , , , ,

SQL Server | SQL Tricks