Blog Archives

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!