Sunday, March 08, 2015

Recursive Function in SQL Query


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!