# Select total number of records from common table expression

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.

-

## 2 Answers

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.

-
yes, thats right , i tried it.. i am searching for any other method –  mahesh Apr 17 '12 at 6:41

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

-