Thursday, August 6, 2009

SQL Tip: Custom Paging from T-SQL

In ASP.NET programming it is a big problem when we will have to show lots of data . for eg. when we want customer list or users list.. etc. Usually we display the list in normal gridview and use gridview's default paging feature.

But what it actually does is that it loads all the data in the page and then does the paging even though we are not seeing the whole no of rows at a time. There are a lot of possible ways to do custom paging. One of the simplest and effective way of doing custom paging is from SQL itself, so that it fetches only the required no of rows from database . The below procedure helps us to achieve Custom paging.

Procedure
Create Procedure Paging
@PageSize int,
@PageNo int
As
Begin

Declare @sql varchar(200)

set @sql='Select * FROM (SELECT TOP
'+ @PageSize + ' * FROM (SELECT TOP '+ @No + ' *
FROM TableName ORDER BY ColumnName) AS t1 ORDER BY ColumnName DESC) AS t2
ORDER BY ColumnName'

exec (@
sql)
End

Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

0 comments : on " SQL Tip: Custom Paging from T-SQL "