Multiple Select Statements using SQL Server 2005 "WITH" Statement

Question

I am trying to use the "WITH" statement in SQL Server 2005. It seems to work fine if I do something like:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
SELECT Name FROM MyBigProducts

But it fails if I try to use multiple select statements like:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
SELECT Name FROM MyBigProducts
SELECT Count(*) FROM MyBigProducts

and the error message is "Invalid object name 'MyBigProducts'".

Is there something I can do to increase the scope of the "MyBigProducts" table to include both of the select statements?

My example is a simplification of my actual code causing the problem, so I'm not discounting the possibility that the above trivial example should work and that there is another bug in my SQL.

I have tried wrapping a BEGIN and END around the two SELECT statements, but the parser could not compile it.

Answer 1

I believe that Common Table Expressions are only valid for immediate use which is why you are getting an error for the "SELECT Count(*) FROM MyBigProducts". In order to reuse a CTE you should use a temporary table instead

DECALRE @BigProducts TABLE (...[declaration omitted]...)

INSERT INTO @BigProducts
SELECT * 
FROM Products 
WHERE Size='Big'


SELECT Name FROM @BigProducts
SELECT Count(*) FROM @BigProducts

Please correct me if I am wrong.

Answer 2

CTE have statement local scope and visibility. If you want greater scope and visibility for a table expression, you would need to make the CTE into a View or table-valued function.

Alternatively, you can materialize the expressions results into a table variable, which is local to the current batch or a temporary table whose lifetime is also local to the current batch, but whose visibility extends to the whole session.

Answer 3

As Kane said, the CTE is only available in the SQL statement where it is written. Another possible solution, depending on the specifics of your situation, would be to include the COUNT(*) in the single query:

;WITH MyBigProducts AS
(
     SELECT
          Name,
          COUNT(*) OVER () AS total_count
     FROM
          Products
     WHERE
          Size = 'Big'
)
SELECT
     Name,
     total_count
FROM
     MyBigProducts