How can I reuse a Common Table Expression

Question

I am using a Common Table Expression for paging:

with query as (
  Select Row_Number() over (Order By OrderNum ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
)
Select * from query where TableRowNum between 1 and 25 Order By TableRowNum ASC

Immediately after making this query, I make make an almost identical query in order to retrieve the total number of items:

with query as (
  Select Row_Number() over (Order By OrderNum ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
)
Select Count(*) from query

I have tried combining these together (ie: define the CTE, query the data and then query the Count, but when I do this, I get an error message "Invalid object name 'query'" in response the the second query (the Count).

Is there any way to combine these two queries into one, to save a round-trip to the DB?

Answer 1

If you do not require them in 2 different queries, you can try

;with query as (
  Select Row_Number() over (Order By UserID ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
),
totalCount AS (
    SELECT COUNT(1) Total FROM query
)
Select  query.*,
     Total
from    query, totalCount 
where   TableRowNum 
between 1 and 25 
Order By TableRowNum ASC

If you do require 2 different queries, rather use a table var

DECLARE @User TABLE(
     TableRowNum INT,
     FirstName VARCHAR(50),
     LastName VARCHAR(50)
)
;with query as (
  Select Row_Number() over (Order By UserID ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
)
INSERT INTO @User
SELECT  TableRowNum,
     FirstName,
     LastName
FROM    query

SELECT  *
FROM    @User
where   TableRowNum 
between 1 and 25 
Order By TableRowNum ASC

SELECT COUNT(1) FROM @User