With SQL 2005 you can JOIN to a table valued user defined function (UDF)

We wanted this forever. They gave it to us with SQL 2005. I presented on it in 2005. Now, three years later, I am finally sharing it! Can you imagine? My code here is based on the Northwind data. Here it is:

/*
    Table-Valued User-Defined-Functions
*/

CREATE FUNCTION MyTableFunction (@id int)
RETURNS @Return Table(EmployeeId int, Title varchar(25))
Begin
    Insert Into @Return 
    Select 
         EmployeeId
        ,Title 
    From Employees
    Where 
        EmployeeId = IsNull(@id, EmployeeId);
    Return;
End

-- simple use (no dbo prefix)

Select * From MyTableFunction(null)

Select * From MyTableFunction(1)

-- fails with join

Select
     [e].EmployeeId
    ,[f].Title As [TitleFromFunction]
    ,[e].FirstName
    ,[e].LastName
From
    Employees [e]
    JOIN MyTableFunction([e].EmployeeId) As [f] ON [f].EmployeeId = [e].EmployeeId

-- success with apply

Select
     [e].EmployeeId
    ,[f].Title As [TitleFromFunction]
    ,[e].FirstName
    ,[e].LastName
From
    Employees As [e]
    CROSS APPLY MyTableFunction([e].EmployeeId) As [f]

DROP FUNCTION MyTableFunction