Many-to-many to recursive CTE in SQL Server

Question

As I understand it, any many to many relationship is a hierarchy if you define one part as parent and another as child.

I have a situation where I need to get the children of an object that is a mapped to itself in a many to many table, but I am having a really hard time writing a recursive CTE for it.

For example, lets say I have:

ParentId           ChildId
========           =======
2                  20
2                  30
5                  50
20                 200
21                 201
30                 300
31                 301

For the root level, the rule I was using was that it is a root object, if its ID is not in second column (ChildId), but this is problematic because, as you can see from example above, I can have multiple records for root (Id: 2)

For the recursion part, no matter what I tried I either got infinite recursion or the recursion did not work at all.

So I am completely stuck.

UPDATE Just adding more clarification. I am building a stored procedure which gets passed in a parentId and which has the recursive CTE inside. The output I am trying to get is, for passed in parentId of 2, using the sample above, I would like:

ParentId           ChildId           Level
========           =======           =====
2                  20                0
2                  30                0
20                 200               1
30                 300               1

Here is some of my code:

alter procedure [dbo].[FindChildren]
    @Id bigint
as
begin

    ;with DocumentHierarchyCTE (ParentId, ChildId, LevelNumber)
    as
    (
        select 
            th.ParentDocumentId as ParentId,        
            th.ChildDocumentId as ChildId,
            0 as LevelNumber            
        from dbo.DocumentHierarchy th 
        where                       
            th.ParentDocumentId = @Id
            and not exists(select 1 from dbo.DocumentHierarchy tz where tz.ChildDocumentId = th.ParentDocumentId )
        union all
        ???
    )
    select *    
    from DocumentHierarchyCTE d
    option (maxrecursion 0)
end
go

Answer 1

This is just a typical recursive cte of which there are thousands of examples all over the place. This returns what I think you want.

declare @Something table
(
    ParentId int
    , ChildId int
)
insert @Something values
(2, 20)
, (2, 30)
, (5, 50)
, (20, 200)
, (21, 201)
, (30, 300)
, (31, 301)

declare @ParentID int = 2
;

with MyCTE as
(
    select *, MyLevel = 0
    from @Something s
    where s.ParentId = @ParentID
    AND ParentID not in
    (
        Select s2.ChildId
        from @Something s2
    )
    union all
    select s3.*, c.MyLevel + 1
    from @Something s3
    join MyCTE c on c.ChildId = s3.ParentId
)

select *
from MyCTE