How to use parameterized UDF in a join query?

I have a table of employees and a User Defined Function udf_GetEmpQualifiedUnits which takes an employee ID and returns all the Units in which that employee can work. I want to get all the employees who are active and their qualified units. The UDF returns EmpID and UnitID (EmpID is same as given as parameter).

Now when I execute the following script;

Code Snippet

select distinct E.EmpID, EU.UnitID

from tblEmp E

inner join udf_GetEmpQualifiedUnits(E.EmpID) EU on E.EmpID = EU.EmpID and E.EmpActiveFlg <> 0

I get this error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "E.EmpID" could not be bound.

I do not want to use Cursors/loops as I am having the same above problem in many of my queries.

It is quite possible that I am unaware of the syntax or other things that could solve my problem.

Thank you.

You can't do this..Rewrite your logic of udf_GetEmpQualifiedUnits on your join query.

|||You can do this if you are using 2005...you will need to use the APPLY operator rather than a join operator. Heres an article I wrote a while back that explains how it works:
http://articles.techrepublic.com.com/5100-9592-6108869.html

Tim