An inline user-defined function is a type of function which returns a table data type instead of just a scalar value and can be used to achieve the functionality of parameterized views. An inline user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. The table returned by an inline user-defined function can be referenced in the FROM clause of a Transact-SQL statement.
Performing a JOIN with an inline user-defined function where the value passed as a parameter to the function comes from the table being joined with it, the INNER JOIN (or LEFT OUTER JOIN) cannot be used as it will give an error of
Msg 4104, Level 16, State 1, Line 2
The multi-part identified could not be bound.
To illustrate, given the following tables and inline user-defined function:
CREATE TABLE [dbo].[MLBTeam] (
[TeamCode] CHAR(3),
[TeamName] VARCHAR(50)
)
CREATE TABLE [dbo].[MLBPitcher] (
[PitcherName] VARCHAR(50),
[TeamCode] CHAR(3),
[ERA] DECIMAL(6, 3)
)
CREATE FUNCTION [dbo].[ufn_GetTopPitchers] ( @TeamCode CHAR(3) )
RETURNS TABLE AS
RETURN (
SELECT TOP 3 [TeamCode], [PitcherName]
FROM [dbo].[MLBPitcher]
WHERE [TeamCode] = @TeamCode
ORDER BY [ERA] )
The following INNER JOIN statement will generate an error:
SELECT A.[TeamCode], A.[TeamName], B.[PitcherName]
FROM [dbo].[MLBTeam] A INNER JOIN [dbo].[ufn_GetTopPitchers] ( A.[TeamCode] ) B
ON A.[TeamCode] = B.[TeamCode]
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "A.TeamCode" could not be bound.
To perform a join between a table and an inline user-defined function where the value passed to the function parameter comes from a column in the table, the CROSS APPLY operator will be used:
SELECT A.[TeamCode], A.[TeamName], B.[PitcherName]
FROM [dbo].[MLBTeam] A CROSS APPLY [dbo].[ufn_GetTopPitchers] ( A.[TeamCode] ) B