Click here to Skip to main content
13,197,527 members (44,474 online)
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 4 Jan 2013

Dynamic Pagination

, 4 Jan 2013
Rate this:
Please Sign up or sign in to vote.
Dynamic paging in grids


This tip will provide developer do little wrap around T-SQL code and get pagination done easily on top of existing code probably.


Dynamic paging in grids is cumbersome and if someone wants to implement custom paging then that has no built in support in T-SQL and using paging for 3rd party controls is sometime is not easy, when it comes to sorting on columns of grid and doing paging makes it more difficult. I found it also slow performing at times

Using the code 

For handling pagination as I did it in one of our cloud based Products. I changed generic procedure that queries internally respective table to include 2 params @PageNo int=Null, @PageSize int=20

query would include 2 columns
,RANK() OVER (ORDER BY a.OIT_dbf_Id desc) AS Rank ,((RANK() OVER (ORDER BY a.OIT_dbf_Id desc))/(@PageSize)) + 1 as Page

Where clause will include Where Rank between

IsNull(case when
@PageNo is Null then @PageNo else case when @PageNo = 1 then 1 else
(@PageNo-1)*@PageSize +1 end end,Page)
IsNull(case when @PageNo is Null
then @PageNo else @PageNo * @PageSize end,Rank)
so when I want particular page I would just set the procedure filter accordingly as well I can set pagesize.

Select * from ( 
select TOP 100 Percent
as TaskID,
, RANK()OVER (ORDERBY a.OIT_dbf_Id desc)ASRank
,((RANK()OVER (ORDERBY a.OIT_dbf_Id desc))/(@PageSize))+ 1 as Page
OIT_tbl_Tasks a Where
a.OIT_dbf_Id =IsNull(@TaskID, a.OIT_dbf_Id) order by
a.OIT_dbf_Id desc
Where Rank betweenIsNull(case when @PageNo isNull then @PageNo else 
							case when @PageNo = 1 then 1 else (@PageNo-1)*@PageSize +1 end
			      end,Page)andIsNull(casewhen @PageNo isNullthen @PageNo else @PageNo * @PageSize end,Rank)

Points of Interest

This I used and found performance improvement is pagination by 2-3 times compared to 3rd party controls. I am going to add caching to it i.e. getting next and prev page and also try out jQuery datatables etc..


This is just first version released for your opinion


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

India India
I work as CTO for Orwell IT Solutions Pvt Ltd.
I like to read about new technologies and experiment, I am always watchful and also mentor newbies joined in organization. I have blog on wordpress.

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171020.1 | Last Updated 4 Jan 2013
Article Copyright 2013 by RahulAJoshi
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid