Getting rowcount of a common table expression CTE into a parameter for paging

Question

My friend here is coding a web page with a datagrid that has paging. We were able to get the total pagecount into a column as a window function but we can't figure out how to get it into a parameter. Seeing the code will make more sense:

    DECLARE @StartRow INT
    DECLARE @EndRow INT
    DECLARE @PerPage INT
    DECLARE @PageNumber int
    SET @PerPage = 30
    SET @PageNumber = 1
    SET @StartRow = ( ( @PageNumber - 1 ) * @PerPage ) + 1
    SET @EndRow = ( ( @PageNumber ) * @PerPage ) ;
    WITH    cte
              AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY Name ) [row]
                            , Name
                            , COUNT(*) OVER ( ) AS [rowcount]
                   FROM table )

    SELECT row, Name, ( [rowcount] / @PerPage ) + 1 AS [pages]
    FROM cte
    WHERE   row BETWEEN @StartRow AND @EndRow
                OR ( @PageNumber = -1 )

I can't get the parameter from the last select because you can't set parameters when you're also returning values. I was hoping there's be some way to do this but in the meantime (which may be an equally good solution) we are just returning this in the dataset and pulling the pages count in code from the dataset instead of through an output parameter. Make sense? Let me know if you know of a way to get this into a parameter! Thanks!

Answer 1

This small change should do it.

, COUNT(*) OVER (PARTITION BY NULL) AS [rowcount]

Answer 2

Unfortunately, all avenues reduce to the same thing: trying to set a value in a select while also outputting data, a verboten combination. My eventual solution to using an output parameter for the total count was to insert the paged data into a table variable, which made it possible to both select the data and set the output parameter. (A temporary table would also have sufficed.)