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 COUNT(1) FROM Employees e2
WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,
FROM Employees e
SELECT LastName, FirstName, NumberOfSubordinates