SQL Server Using Recursive UDF in innerjoin

Below query is not working. It says incorrect syntax near "." (erc.bossId), Please help

select e.employeeId, a.address from empReportingChain erc
in开发者_开发技巧ner join employee e on e.employeeId = (select top(1) emp_id_PARENT from fn_core_ReturnEmpParent(erc.bossId) order by rolup_level desc)
inner join address a on a.employeeid= e.employeeid

Thanks, Shwetabh

From MSDN:

When a user-defined function that returns a table is invoked in the FROM clause of a subquery, the function arguments cannot reference any columns from the outer query.

the inner select statement has (erc.bossId) after the from clause which is causing your issue.

Are you trying to limit the inner query by attaching to the outer? If not, remove (erc.bossId) and your query will execute.

If you are, then assuming your fn_core_ReturnEmpParent table has a bossId column, the following should work;

select e.employeeId, a.address 
from empReportingChain erc
inner join employee e on e.employeeId = (
    select top(1) emp_id_PARENT 
    from fn_core_ReturnEmpParent rep
    WHERE erc.bossId = rep.bossId
    order by rolup_level desc
)
inner join address a on a.employeeid= e.employeeid