What i wanted to do was to make a function that will give me all the people under 1 manager.
So there could be managers under 1 manager ans so on.
This is what i did.
alter FUNCTION fGetManager
(
@EmployeeID bigint
)
RETURNS TABLE
AS
RETURN
(
WITH DR ( KID, NAME, Manager) AS (
SELECT KID, NAME, Manager FROM Employees K WHERE KID = @EmployeeID
UNION ALL
SELECT K.KID,
K.NAME, K.Manager FROM Employees K INNER JOIN DR D ON K.Manager= D.KID
)
SELECT kid FROM DR
)
GO
--Using this function
--SELECT * FROM Employees where kid in (
select * from [dbo].[fGetManager] (2))
Enjoy IT!