How to reference one CTE twice?

I have a very fat common table expression which includes row numbers so that I can return a paged result set. I also want to return the total nu开发者_StackOverflow中文版mber of records that match the query before I page the result set.

with recs as (select *, row_number() over (order by id) as rownum from ......)
select * from recs where rownum between @a and @b .... select count(*) from recs

Obviously my query above is patchy, but it's just for illustrating my point. I want a page of results AND the total number of matches. How do I do this without having to literally copy and paste the entire 20+ line CTE?

Don't think you can. From MSDN

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Emphasis on "single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."

This might be a situation where you want to use a Temporary Table.

CREATE TABLE #Recs
{
  .....
}
INSERT INTO #Recs
select *, row_number() over (order by id) as rownum from ......

If you don't know the structure of the table before hand you can use this form to create a temporary table:

select *, row_number() over (order by id) as rownum INTO #Recs from ......

You will be able to use the Temporary table in the manner you have described above.

You can use commas to create multiple CTEs that references the CTEs Above.

Just to illustrate what I mean:

with recs as (
select 
    *, 
    row_number() over (order by id) as rownum from ......
    ),
counts as (
    select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts 
where rownum between @a and @b .... 

This is not the a good solution.

The best solution I found to have the total count in a CTE without counting the records is described in this article.

DECLARE @startRow INT; SET @startrow = 50;
WITH cols
AS
(
    SELECT table_name, column_name, 
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq, 
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq

You could append a field that has the total rows in it, of course it will be on every row

select recs.*,totalrows = (select count(0) from recs) 
from recs

This is the best:

;WITH recs AS
(SELECT a,b,c,
      row_number() over (
                         ORDER BY id) AS RowNum,
                   row_number() over () AS RecordCount
FROM ......)
SELECT a,b,c,rownum,RecordCount FROM recs
WHERE rownum BETWEEN @a AND @b

This is how we deal with paging (without session management for now) in a production environment. Performs as expected.

DECLARE
   @p_PageNumberRequested  int = 1,
      -- Provide -1 to retreive all pages with all the rows.
   @p_RowsPerPage          int = 25

;WITH Numbered AS (
SELECT
   ROW_NUMBER() OVER (ORDER BY YourOrdering) AbsoluteRowNumber
,  COUNT(1) OVER () TotalRows
,  YourColumns
FROM
   YourTable
),
Paged AS (
SELECT
   (AbsoluteRowNumber - 1) / @p_RowsPerPage + 1 PageNumber,
   *
FROM
   Numbered)
SELECT
   ROW_NUMBER() OVER(PARTITION BY PageNumber ORDER BY AbsoluteRowNumber) RowNumberOnPage,
   *
FROM
   Paged
WHERE
      PageNumber = @p_PageNumberRequested
   OR
      @p_PageNumberRequested = -1
ORDER BY 
   AbsoluteRowNumber