我想要做类似的事情
select * from tvfHello(@param) where @param in (Select ID from Users)
我想要做类似的事情
select * from tvfHello(@param) where @param in (Select ID from Users)
你需要使用CROSS APPLY来实现这个目标。
select
f.*
from
users u
cross apply dbo.tvfHello(u.ID) f
CROSS APPLY
,现在我看到了它的优点,非常感谢@kristof。 - Frank Myat ThuCREATE 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)
根据Kristof的专业知识,如果您想获取多个值,您可以尝试以下操作:
select *
from HumanResources.Employee e
CROSS APPLY EmployeeById(e.EmployeeId)
WHERE e.EmployeeId in (5,6,7,8)
在我看来,那看起来还不错,只是你应该始终在函数前加上它们的模式(通常为dbo)。因此查询应该是:
SELECT * FROM dbo.tvfHello(@param) WHERE @param IN (SELECT ID FROM Users)