LISTING 3: Using a Loop to Get Ancestor

CREATE FUNCTION dbo.ufn_GetAncestor2
(
  @empid AS int,
  @lvl   AS int = 1  -- levels above employee
)
RETURNS int
AS
BEGIN
  
  IF @lvl IS NULL or @empid IS NULL or @lvl < 0
    RETURN NULL

  DECLARE @mgrid AS int
  SET @mgrid = @empid

  WHILE @lvl > 0 AND @mgrid IS NOT NULL
    SELECT @mgrid = mgrid, @lvl = @lvl - 1
    FROM Employees WHERE empid = @mgrid

  RETURN @mgrid

END