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.