depending on what you want to achieve then you may want to add a hierarchyid column to your table and then use the .GetDescendant / .GetAncestor functions rather than using a CTE.

although these are table changes. if that isn't possible then stick with the CTE. unless you are looking to port this code to MYSQL or some other format ??? is this why you are asking the question ?