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.