Modify FindAll function to a DoesExist function in SQL Server

Question

I have the following recursive function:

ALTER FUNCTION [dbo].[ListAncestors] 
(   
    @Id int
)   
RETURNS TABLE  
As  
RETURN  
(   
    WITH cte As  
    (   
        SELECT
            UserId,
            ManagerId,  
            Forename,
            Surname  
        FROM  
            dbo.Users   
        WHERE  
            UserId = @Id   

        UNION ALL  

        SELECT  
            T.UserID,
            T.ManagerID,  
            T.Forename,   
            T.Surname  
        FROM  
            cte As C INNER JOIN dbo.Users As T   
            ON C.UserID = T.ManagerID   
    )   
    SELECT
        Forename,
   Surname 
    FROM  
        cte
);

Basically what it does is returns the names of all users below the specified user (based on their ID). What I would like to do is modify this function and create another function which does a check if a specific userID is an ancestor of another.

I imagine the signature would look something like:

CREATE FUNCTION IsAncestor(@Id int, @AncestorId int) RETURNS BIT

Answer 1

How about:

WHILE @Id IS NOT NULL AND @Id <> @AncestorId
BEGIN
 SET @Id = (
  SELECT ManagerId FROM dbo.Users WHERE UserId = @Id
 )
END

RETURN CASE WHEN @Id IS NOT NULL THEN 1 ELSE 0 END

Answer 2

If we accept that the initial CTE takes an ID and lists all the 'ancestors' of that ID, I think that the following query tests for this relation.

WITH cte As  
(   
    SELECT
        UserId,
        Forename,
        Surname  
    FROM  
        dbo.Users   
    WHERE  
        UserId = @Id   

    UNION ALL  

    SELECT  
        T.UserID,  
        T.Forename,   
        T.Surname  
    FROM  
        cte As C INNER JOIN dbo.Users As T   
        ON C.UserID = T.ManagerID and C.UserID <> @ancestorID
)   
SELECT CAST (COUNT(*) as BIT) FROM cte WHERE UserID = @ancestorID

It's a bit odd though, since given the initial function a person is in the 'ancestor' relation with themselves.

Incidentally, I removed the ManagerID from the select statements in the CTE since it isn't necessary