Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple Select Statements using SQL Server 2005 "WITH" Statement

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.

like image 614
Brian Hinchey Avatar asked Jun 05 '09 02:06

Brian Hinchey


2 Answers

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
like image 64
Tom H Avatar answered Oct 21 '22 06:10

Tom H


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.

like image 20
Kane Avatar answered Oct 21 '22 08:10

Kane