List All Employee under my control

Hi,

I have a table employee with two fields (empId, and Supervisor)

what i want is to list all employees under a given empId

example

Table: Employee
EmpId, Supervisor
1, Null
2, 1
3, 1
4, 2
5, 2
6, 3
7, 4
8, 4
9, 5
10, 5
11, 6
12, 6
13, 6

request of the employees under control of 4?
Result should be: 4, 7, 8

request of the employees under control of 2?
Result should be: 2, 4, 5, 7, 8, 9 , 10

request of the employees under control of 3?
Result should be: 3, 6, 11, 12 , 13

thanks a lotsdeclare @.SupervisorID int
set @.SupervisorID = 3

declare @.Subordinates table (EmpID int)

insert into @.Subordinates (EmpID)
select EmpID
from Employees
where Supervisor = @.SupervisorID

while @.@.Rowcount > 0
insert into @.Subordinates (EmpID)
select Employees.EmpID
from Employees
inner join @.Subordinates Subordinates on Employees.Supervisor = Subordinates.EmpID
where not exists(select * from @.Subordinates CurrentEmpIDs where Employees.EmpID = CurrentEmpIDs.EmpID)

select * from @.Subordinates

blindman