Jerry Nixon @Work: With SQL 2005 you can JOIN to a table valued user defined function (UDF)

Jerry Nixon on Windows

Friday, March 14, 2008

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