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