How can i detect whom a person reports to using a self-referencing employee table?

I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect reports. That is to say I can pass in a single employee and get back a listing of them and everyone under them.

I want to create a similar function that would allow me to take a boss and employee param and detect if the employee reports into the boss (or the boss param is indeed the boss - direct or indirect of the employee)

whats the best way to accomplish this logic using the DB structure outlined below as what I have today?

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] NOT NULL,
    [managerId] [int] NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [FullName] [nvarchar](100) NOT NULL,
    [CorpEmailAddress] [nvarchar](510) NULL
) ON [PRIMARY]

ALTER FUNCTION [dbo].[fnGetEmployeeHierarchy]   
(    
  @EmployeeId int = null  
)  
RETURNS TABLE   
AS  
RETURN   
(  
  WITH yourcte AS  
  (  
    SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name  
    FROM Employees  
    WHERE EmployeeId = isnull(@EmployeeId,EmployeeId)  
    UNION ALL  
    SELECT e.EmployeeId, e.ManagerID, e.AMRSNTID, e.FullName--, e.Name  
    FROM Employees e  
    JOIN yourcte y ON e.ManagerID = y.EmployeeId  
  )  
SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name  
FROM yourcte  
)

Since to me it's already a well known and solved problem, and since the commentary on here seems to agree with me:

https://stackoverflow.com/questions/3859882/sql-recursive-function-to-find-managers

Are you wanting to return the results for each lowest employee in the set, and that's what's making it hard?

StackExchange Database Administrators Q#1137, answer score: 3