title: SQL how to reuse temp table
body: <p>I have a complex query for selecting paged records with 'with', I want to reuse last temp table for another use like record count:</p>
  
  <pre><code>;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 &gt; @FirstRec 
      And RowNumber &lt; @LastRec
  
  
  
  SELECT Count(*) FROM #ProductsNumbered 
  </code></pre>
  
  <p>Last statement returns an error Invalid object name '#ProductsNumbered'</p>
  
  <p>How can I reuse #ProductsNumbered to avoid calculating whole SP just for a simple Counter?</p>
  
  <pre><code>SELECT Count(*) FROM #ProductsNumbered 
  </code></pre>