Select total number of records from common table expression

Question

We are using common table expressions to temporarily save the search results.

  WITH Results (
                 ------
                  )
  AS
    (
          select ---
        )

 SELECT * FROM Results 

        WHERE Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount

Here i want to get the toatal number of records in the Results common table expression in a variable @RowCount. Is there any way to get it without using table variable or temp table.

Answer 1

You can not have a query that returns a result set and at the same time assigns a variable.

If you try something like this

declare @Var int

select @Var = Col1, Col2
from ATable

You get the error message

Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Answer 2

No, because you can't mix assignments and result set generation

But what you can do is make the total row count into an extra column

WITH Results AS
(
    select
        ---,
        COUNT(*) OVER () AS TotalRowCount -- this
    ...
)
SELECT
   *
FROM 
   Results 
WHERE 
   Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount