Need to get descendent items list in stored procedure

Question

Description:

There is table wich consist of two columns (ParentId and ChildId) that displays hierarchy of some entities. Each Id could be presented in ParentId column only one time. That means that each entity have only one child entity.

Problem: I need to check whether entity(its Id) is in descendants list of parent entity.

Answer 1

DECLARE @parentId INT
DECLARE @childId INT
DECLARE @targetChildId INT
SET @targetChildId=<put id of a child you want to find>
SET @parentId=<put id of a parent you are looking child for>
SET @childId=0

WHILE (@childId<>@targetChildId)
    BEGIN
        IF(EXISTS(SELECT ChildId FROM Hierarchies WHERE ParentId=@parentId))
        BEGIN
            SET @childId=(SELECT ChildId FROM Hierarchies WHERE ParentId=@parentId)
            SET @parentId=@childId
        END 
        ELSE
        BEGIN
            SET @childId=0
            BREAK
        END
    END
PRINT @childId

It returns 0 if target child not found in target parent.

Answer 2

Sample data:

CREATE TABLE [dbo].[EntityHierarchy]
(
    [EntityId] INT,
    [ChildEntityId] INT
)

INSERT  [dbo].[EntityHierarchy]
VALUES  (1, 2),
        (2, 3),
        (3, 4),
        (4, 1) -- Cycle

Find circular relationships:

DECLARE @SearchEntityId INT = 1

;WITH [cteRursive] AS
(
    SELECT  1 AS [ROW_NUMBER],
            [ChildEntityId] AS [EntityId]
    FROM [dbo].[EntityHierarchy]
    WHERE [EntityId] = @SearchEntityId
    UNION ALL
    SELECT  r.[ROW_NUMBER] + 1,
            h.[ChildEntityId]
    FROM [cteRursive] r
    INNER JOIN [dbo].[EntityHierarchy] h 
        ON r.[EntityId] = h.[EntityId]
    WHERE h.[ChildEntityId] <> @SearchEntityId
)
SELECT h.*
FROM [cteRursive] r
INNER JOIN [dbo].[EntityHierarchy] h 
    ON r.[EntityId] = h.[EntityId]
WHERE r.[ROW_NUMBER] = (SELECT MAX([ROW_NUMBER]) FROM [cteRursive])

I'm using a recursive CTE to list the descendants. The child of the last descendant either creates a cycle or not.