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