Try this, it's not all that much differnet from what you already have:
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
D.employeeid
From
directory D
Left Join
@temp T
On
D.higmanagerempid = T.employeeid
Where
T.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
R.empployeeid = D.employeeid
Where
len(D.mail)>0
RETURN
END
I always use joins when I can because I find htem easier to read than subselects. There probably is a better way to get through the heirarchy but this should work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question