title: Possible to return records and count from a CTE?
body: <p>Say I have a stored proc that has a return param called <code>Count</code> and it is made up of the following SQL:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Currently this will break because <code>Temp</code> 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 <strong>and</strong> return the rows  from 10-20?  </p>
  
  <p>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.</p>