Possible to return records and count from a CTE?

Question

Say I have a stored proc that has a return param called Count and it is made up of the following SQL:

with temp as
(
    SELECT ROW_NUMBER() OVER( ORDER BY o.createDate) as rowNum,
           o.orderId
    FROM Orders as o

)
SELECT * FROM temp where rowNum BETWEEEN 10 and 20
SELECT @Count = COUNT(*) FROM Temp

Currently this will break because Temp is gone after the first select. Is it possible for me to set the value of the return parameter to the total number of rows in my CTE and return the rows from 10-20?

I'm thinking I might have to do this using a temp table but I'm curious if it can be done using a CTE.

Answer 1

You could put the value directly into temp:

with temp as
(
    SELECT ROW_NUMBER() OVER( ORDER BY o.createDate) as rowNum,
           count(*) over () as cnt,
           o.orderId
    FROM Orders as o

)
SELECT * FROM temp where rowNum BETWEEEN 10 and 20

You can then read the cnt with every row.