Help ! How do I get the total number rows from my SQL Server paging procedure?

Question

Ok I have a table in my SQL Server database that stores comments. My desire is to be able to page though the records using [Back],[Next], page numbers & [Last] buttons in my data list. I figured the most efficient way was to use a stored procedure that only returns a certain number of rows within a particular range. Here is what I came up with

@PageIndex INT, 
@PageSize INT,
@postid int


AS
 SET NOCOUNT ON  
 begin

WITH tmp AS ( 
SELECT comments.*, ROW_NUMBER() OVER (ORDER BY dateposted ASC)  AS  Row
    FROM    comments
    WHERE     (comments.postid = @postid))

SELECT tmp.*
FROM tmp
WHERE Row between 

(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

end

RETURN 

Now everything works fine and I have been able implement [Next] and [Back] buttons in my data list pager. Now I need the total number of all comments (not in the current page) so that I can implement my page numbers and the[Last] button on my pager. In other words I want to return the total number of rows in my first select statement i.e

  WITH tmp AS ( 
    SELECT comments.*, ROW_NUMBER() OVER (ORDER BY dateposted ASC)  AS  Row
        FROM    comments
        WHERE     (comments.postid = @postid))
set @TotalRows = @@rowcount

@@rowcount doesn't work and raises an error. I also cant get count.* to work either.

Is there another way to get the total amount of rows or is my approach doomed.

Answer 1

I've dealt with this very problem and in the end I found a handful of solutions none of which are spectacular but do the job:

1. Query twice
2. Return the count as one of the columns
3. Stuff the results into a temporary table while returning the count as a column

In the first solution you would do something like:

    ...
    , @Count int OUTPUT
AS 
Select @Count = (
                Select Count(*)
                From comments
                Where comments.postid = @postid
                    And Col1 = ... And Col2 = ...
                )

With NumberedResults As
    (
    Select ...
        , ROW_NUMBER() OVER( ORDER BY dateposted ASC ) As Num
    From comments
    Where Col1 = ... And Col2 = ...
    )
Select ...
From NumberedResults
Where Num Between ( @PageIndex - 1 ) * @PageSize + 1 and @PageIndex * @PageSize

The obvious downside is that if the query is expensive, you do it twice.

In the second solution, you simply return the count as part of the results. You would then pick off the count from the first record in your business tier code. The advantage is that you only do an expensive query once. The downside is you return an extra four bytes for every row in the result.

With NumberedResults As
    (
    Select ...
        , ROW_NUMBER() OVER( ORDER BY dateposted ASC ) As Num
    From comments
    Where Col1 = ... And Col2 = ...
    )
Select ...
    , ( Select Count(*) From NumberedResults ) As TotalCount
From NumberedResults
Where Num Between ( @PageIndex - 1 ) * @PageSize + 1 and @PageIndex * @PageSize

The third solution is a variant of the second in that you stuff the results into a temp table and set your out parameter from the first record

    ...
    , @TotalCount int OUTPUT
AS

Declare @PagedResults Table (
                            Col1 ...
                            , ...
                            , TotalCount int
                            )
With NumberedResults As
    (
    Select ...
        , ROW_NUMBER() OVER( ORDER BY dateposted ASC ) As Num
    From comments
    )
Insert @PagedResults( Col1...., TotalCount )
Select ...
    , ( Select Count(*) From NumberedResults ) As TotalCount
From NumberedResults
Where Num Between ( @PageIndex - 1 ) * @PageSize + 1 and @PageIndex * @PageSize

Set @TotalCount = ( Select TOP 1 TotalCount From @PagedResults )

Select ...
From @PagedResults