SQL Server 2005 TSQL Enhancements: New APPLY Operator

Suppose you have a query that returns a data set and you want for each row returned you want to invoke a function or query that returns a table (table-valued function or query ), the new APPLY operator allowa you to do this

for example if you have a table Employees(EmpID,Name,ManagerID)
and you have the following function that takes an Employee id as a parameter and returns a table of employees under this employee and their level in the hierarchy


CREATE FUNCTION [dbo].[GetEmps](@EmpID [int])
RETURNS @RES TABLE (
Empid INT ,
EmpName nvarchar(50),
Level INT
)

AS
begin

with Emps_CTE (EmpID,Name,Level)
as
(
select EmpID,Name,0 as Level FROM Employees WHERE ManagerID is null and EmpID=@EmpID
Union All
Select Employees.EmpID,Employees.Name,Level + 1 FROM Employees Join Emps_CTE ON
Emps_CTE.EmpID=Employees.ManagerID

)

INSERT INTO @RES
select * FROM Emps_CTE
return


The following query will return for each employee the employees working under him and their level

SELECT Employees.EmpID,Employees.Name ,FollowsEmps.Empid as FollowEmpID,
FollowsEmps.EmpName as FollowEmpName,FollowsEmps.Level as Level

FROM Employees
cross APPLY dbo.GetEmps(Employees.EmpID) as FollowsEmps
end