I took Jack's code, modified it with the changes suggested by Antares and I get this:

Create FUNCTION dbo.udf_GetEMpsBYManager

(

@empid char(6)

)

RETURNS @table_variable TABLE (employeeid char(6), sn varchar(15), givenname varchar(25), mail varchar(50) )

AS

BEGIN

Declare @temp Table (employeeid char(6))

Declare @rows Int

Insert Into @temp

  Select Distinct employeeid

  From directory

  Where higmanagerempid = @empid

While @rows > 0

Begin

  Insert Into @temp

    Select Distinct E.employeeid

    From

    (

       Select Distinct D.employeeid

       From directory D Left Join @temp T

          On D.higmanagerempid = T.employeeid

    )

   As E LEFT JOIN @temp T2

       On E.employeeid = T2.employeeid

  Where T2.employeeid Is NULL

Set @rows = @@rowcount

End

Insert Into @table_variable

  Select D.employeeid, D.sn, D.givenname, D.mail

  From  @temp T Join Directory D

  On T.employeeid = D.employeeid

  Where len(D.mail)>0

RETURN

END

Executing this code only returns the first level of direct reports to the employee_id specified in the UDF parameter (12 employees). My original SP returns 432 employees all the way to the bottom of the chain.

Did I mis-code something? I thought I applied Antares' changes correctly.

nb. Coach James, I can see your point theoretically but I have no idea how to apply it to my case.