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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With