SQL how to reuse temp table

Question

I have a complex query for selecting paged records with 'with', I want to reuse last temp table for another use like record count:

;with ProductCatIDs
    as
    (
       select
          ID, ParentID, Name
       from
          [tbl_Categories]
       where
          ID = @CatID
       union all
       select
          m.ID, m.ParentID, m.Name
       from
          [tbl_Categories] m
       join
          ProductCatIDs on ProductCatIDs.ID = m.ParentID
    ),

    ProductsFiltered as (

SELECT    DISTINCT tbl_Products.*
FROM         tbl_ProductCategories RIGHT OUTER JOIN
                      tbl_Products ON tbl_ProductCategories.ProductID = tbl_Products.ID
WHERE     tbl_Products.Enabled = 'true' and (tbl_ProductCategories.CategoryID IN (

    select id
    from ProductCatIDs

))),

#ProductsNumbered as (

Select *, ROW_NUMBER() OVER (order by ID DESC) as RowNumber FROM ProductsFiltered

    )

SELECT * FROM #ProductsNumbered As tbl_Products
    Where 
    RowNumber > @FirstRec 
    And RowNumber < @LastRec



SELECT Count(*) FROM #ProductsNumbered 

Last statement returns an error Invalid object name '#ProductsNumbered'

How can I reuse #ProductsNumbered to avoid calculating whole SP just for a simple Counter?

SELECT Count(*) FROM #ProductsNumbered 

Answer 1

By not making #ProductsNumbered a part of your Common Table Expression, but inserting into it manually from your ProductsFiltered. Then you should be able to reuse it.

;with ProductCatIDs
 as
  (
   ----SNIPPED TO KEEP POST SMALL-------
  ), ProductsFiltered as (
    ----SNIPPED TO KEEP POST SMALL-------            
  ),
  SELECT *, 
     ROW_NUMBER() OVER (order by ID DESC) as RowNumber 
  INTO #ProductsNumbered 
  FROM ProductsFiltered 

in this way you should have access to your temp table outside the scope of the common table expressions and can run multiple queries on it.