SQL Server recursive query, Which returns all ancestors of an item

We will create a recursive query when getting data as per parent and its level. Mostly we use this query in MLM software (Multilevel marketing software). Using this you can easy to get who is my parent till the level we wanted to find.

declare @CurrentUser int = 9;

;with CteMyParents as (
   select Id, ParentId, Username, 1 as [Level]
   from [User]
   where Id = @CurrentUser
   union all
   select c.Id, c.ParentId, c.Username, p.[Level] + 1
   from [User] c
     join CteMyParents p on p.ParentId = c.Id
)

select *
from CteMyParents

for more info follow this URL:

https://stackoverflow.com/questions/16749095/sql-recursive-query-that-gets-all-ancestors-of-an-item

Create new stored procedure for find above level from binary tree of current user