BaldingLoopMan (12/8/2009)

---

so for example. I have the below. Is this the same basic principle as using an into #tablename and then querrying it? Are the CTE's specific to the runtime of a stored procedure or once their defined thay can be refferenced forever?

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS
(
SELECT
EmployeeID,
LastName,
FirstName,
(SELECT COUNT(1) FROM Employees e2
WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,
ReportsTo
FROM Employees e
)

SELECT LastName, FirstName, NumberOfSubordinates
FROM EmployeeSubordinatesReport

niall.baird (12/8/2009)

---

Seriously? You can only use a CTE in the statement immediately following?
When I read the article, the impression I got was that you could define a CTE at the top of something like a stored proc, then use it all the way through. Is my understanding wrong?

Lynn Pettis(12/8/2009)

---

They can only be referenced by the immediately following SELECT/INSERT/UPDATE/DELETE statement.

niall.baird (12/8/2009)

---

I *can* read Garadin. That is why I was questioning the above statement. Seems to me that defining a #table would be more use, as you can use it in more places - unless of course, I am missing something.