I want to do something like
Select * from tvfHello(@param) where @param in (Select ID from Users)
A:
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)
Matt Hamilton
2008-12-03 00:22:00
I do not think, that would work. if you do not define the @param you will get: Must declare the scalar variable "@param". error. If you define the @param you will get the results to whatever the @param is assigned to (NULL by default). To get the full set of results you need to use CROSS APPLY
kristof
2008-12-03 10:45:38
What are you talking about? I'm simply correcting the query that was offered in the sentence. Rajivdas never stated the intent of his query, so you can't possibly know whether he needs to do a CROSS APPLY or not.
Matt Hamilton
2008-12-03 11:05:45
True, the question is not very clear, I based my answer on comments Rajivdas made to answer by Josh. Look at the comment by NTulip to see how confused some people get. But surely it could have been me who interpreted that wrongly
kristof
2008-12-03 16:05:09
+1
A:
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)
JoshBerke
2008-12-03 00:24:24
in my case I can only the results for employeeId=10, but not for the other value. Any ideas?
DotDot
2008-12-03 00:44:14
sounds like your trying to do something more complex then your question indicated. (Which I had a feeling you were). You want to pass in for example all the userId's...one way would be to build a delmited string and then break it down in the function but that defeats the purpose.
JoshBerke
2008-12-03 03:15:06
Josh. The example that you gave it is actually the same as calling select * from EmployeeById(10) WHERE 10 in (SELECT EmployeeId FROM HumanResources.Employee). To get results for multiple values of parameter you need to use cross apply (SQLServer2005+)
kristof
2008-12-03 10:57:51
A:
just learned that you can select using a dynamic parameter as a column!
awesome
NTulip
2008-12-03 00:28:25
+3
A:
You need to use CROSS APPLY to achieve this
select
f.*
from
users u
cross apply dbo.tvfHello(u.ID) f
kristof
2008-12-03 10:39:59