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

To implement custom paging using in asp.net 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!

Advertisements

About saxenapraveen

I work with Microsoft and have 12 years of experience in developing,designing and leading 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:

WordPress.com Logo

You are commenting using your WordPress.com 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: