Custom paging in SQL Server stored procedure

Microsoft.net provides controls which have paging capability by default – that is they have built in feature that can let you apply data paging by just setting a few properties or with just a few lines of code. The problem with this approach is that all the data is cashed in the memory i.e. using dataset etc, which effects application performance. Custom data paging in SQL Server stored procedure is a solution to this problem. I’ll explain how to perform this in such a way that you can use it either in SQL Server 2000 or SQL Server 2005 or even later versions.

You need to pass three parameters to the stored procedure which are page number, page size, and sort order. Following stored procedure code will return a page at a particular page number with your specified page size, sorted in your specified order i.e. Asc or Desc.

First of all, pass following three parameter to the stored procedure:

@PageNumber Int,
@PageSize Int,
@SortOrder Varchar(10) = 'DESC'

We have to apply this custom data paging on Article table. First create a temporary table in stored procedure.

Declare @TempTable Table
(
TempID int Identity,
ArticleID Int
)


Following code snippet will help you sort the data in your required order and keep it in temp table.

if @SortOrder='DESC'
Begin
Insert Into @TempTable (ArticleID)
Select ArticleID
From Article
Where StatusID=1
Order By
ArticleID Desc
End
Else
Begin
Insert Into @TempTable (ArticleID)
Select ArticleID
From Article
Where StatusID=1
Order By ArticleID Asc
End

The reason to keep the data in temporary table first is just to attach a unique id in a sequence to help get a proper page from the table. As, in your actually table some records might have been removed, so if you would apply paging directly on Article table, it might not return proper set or records for a particular page.

Following two local variables will help you get the proper page.

Declare @StartIndex Int
Declare @EndIndex Int

You can perform custom paging using either 1 based or 0 based index. The formulas for both approaches are as under. You can use any one of your choice.


--If page number is 1 based
Set @StartIndex = ((@PageNumber-1)* @PageSize) +1
Set @EndIndex = @PageNumber * @PageSize

--if page number is 0 bases then
Set @StartIndex = (@PageNumber * @PageSize) + 1
Set @EndIndex = @PageSize * (@PageNumber + 1)

Now, just perform a join of Article and TempTable and get all the records between StartIndex, and EndIndex.

Select E.* From Article E
Inner Join @TempTable T
On E.ArticleID= T.ArticleID
Where T.TempID Between @StartIndex And @EndIndex