Custom Paging stored procedure in Sql Server – Getting total number of records with CTE

To implement custom paging using in gridview/listview/formview controls is very common. Main concept is to fetch records from database which are shown on the page when loaded. clicking on page number will again fetch next set of records.

Here are the steps we need to take

  1. We need to write a stored proc which accepts PageSize and PageIndex as input parameters. these params help in getting the start index and endindex of the result set.
  2. Next step is to create a custom pager control which needs total number of records returned by the same (well, ideally) stored proc so we put an output param say TotalRecords to the same SP.
  3. Here is the main query using Common Table expression introduced in Sql Server 2005

    ;with CTE AS
    select ProductId,Name,<strong> count(*) over(partition by '') as TotalRecords</strong> , ROW_NUMBER() over( order by productid) as rownum
    from Products
    select * from CTE where rownum between @StartIndex and @EndIndex

    Here the interesting part is getting total number of records in the same query while ranking each row with a rownumber. By earlier techniques we need to get total number of records in a seperate query/udf. It really saved me a lot of time and improved the query performance as well.
    In Sql Server 2012 “De nali” version it is going to be even easier to skip certain number of rows and take only specified number of records by using OffSet keyword. I am yet trying to explore those new features. I hope to share more about it in future.

    I hope it helps!


About saxenapraveen

I have 14 years of experience in enterprise software development,designing and leading team to provide reliable and scalable solutions for cloud and device ready businesses.

Posted on April 9, 2012, in .NET, Sql Server and tagged , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: