How to improve user defined function with while on SQL Server?

Question

I have a SQL Server 2008 R2 UDF which performs a kind of recursive loop. I mean, I have a table called Employees where in one of my columns I store another Employee id (his boss).

When I get an employee id, I must be able to know the whole department below him. For example: Employee Joe (ID:1) works for Robert (ID:2) Employee Robert (ID:2) works for Michelle (ID:3)

I must be able to count the salary (let's suppose it's on the same table) of all employees below Michelle, i.e. Robert and Joe.

Up to now, I created a UDF that returns a table with all employee ids below Michelle and use an EXISTS clause on the queries' where but it performs very poorly.

Do you guys have another idea?

Thank you!

Forgot to said I use a while loop on the UDF to retreive every employee — Daniel Martinez, Aug 30 '13 at 06:46
Not sure in your case, but if you need hierarchical data you follow this [post](http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server) — Nilesh, Aug 30 '13 at 06:50
You should probably be using a [CTE](http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx) rather than a `WHILE` loop. Explicit looping is rarely the best way to achieve performance. Can't offer an example though, because you've not shown us any code. — Damien_The_Unbeliever, Aug 30 '13 at 06:51

score 1 · Accepted Answer · answered Aug 30 '13 at 07:01

You should probably use a recursive CTE rather than a WHILE loop to find all of the employees. I don't have your tables or data so I've made some up:

create table Employees (
    ID int not null primary key,
    Name varchar(20) not null,
    BigBossID int null foreign key references Employees(ID),
    Salary decimal(18,4) not null
)
go
insert into Employees (ID,Name,BigBossID,Salary) values
(1,'Joe',2,2.50),
(2,'Robert',3,19000.75),
(3,'Michelle',null,1234567890.00)

And then I can use this query to find all employees below Michelle:

declare @RootID int
set @RootID = 3
;With EmployeesBelowRoot as (
    select ID from Employees where BigBossID = @RootID
    union all
    select e.ID from Employees e inner join EmployeesBelowRoot ebr on e.BigBossID = ebr.ID
)
select SUM(Salary) from Employees where ID in (select ID from EmployeesBelowRoot)

You could (if you think it's worth it) place the CTE (EmployeesBelowRoot) into a UDF and call it with @RootID as a parameter, but I've just put it directly in the query for now.

How to improve user defined function with while on SQL Server?

1 Answers1