CODEWITHSUNDEEP
sql-serversql-server-2005user-defined-functions
R.D
R.D

Reputation: 4871

How to pass parameters to Table Valued Function

I want to do something like

select * from tvfHello(@param) where @param in (Select ID from Users)

Upvotes: 27

Views: 68113

Answers (3)

JoshBerke
JoshBerke

Reputation: 67128

The following works in the AdventureWorks database:

CREATE FUNCTION dbo.EmployeeByID(@employeeID int)
RETURNS TABLE
AS RETURN
(
    SELECT * FROM HumanResources.Employee WHERE EmployeeID = @employeeID
)
GO


DECLARE @employeeId int

set @employeeId=10

select * from 
EmployeeById(@employeeId) 
WHERE @EmployeeId in (SELECT EmployeeId FROM HumanResources.Employee)

EDIT

Based on Kristof expertise I have updated this sample if your trying to get multiple values you could for example do:

select * 
from HumanResources.Employee e
CROSS APPLY  EmployeeById(e.EmployeeId)
WHERE e.EmployeeId in (5,6,7,8)

Upvotes: 5

kristof
kristof

Reputation: 53854

You need to use CROSS APPLY to achieve this

select 
    f.* 
from 
    users u
    cross apply dbo.tvfHello(u.ID) f

Upvotes: 44

Matt Hamilton
Matt Hamilton

Reputation: 204259

That looks ok to me, except that you should always prefix your functions with their schema (usually dbo). So the query should be:

SELECT * FROM dbo.tvfHello(@param) WHERE @param IN (SELECT ID FROM Users)

Upvotes: 0

Related Questions