LISTING 2: Using Recursion to Get Ancestor

CREATE FUNCTION dbo.ufn_GetAncestor
(
  @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

  IF @lvl = 0
    RETURN @empid

  RETURN dbo.ufn_GetAncestor(
      (SELECT mgrid FROM Employees WHERE empid = @empid),
      @lvl -1)

END