set statistics time on
set statistics io on
declare @Topid_in int -- The top level we want to resolve children for
select @topid_in = 1;
with Hierarchycte (id, ParentID, name) as
(select id, parentid, name
from cat
where id = @topid_in
union all
select cat.id, cat.parentid ,cat.name
from cat
inner join hierarchycte
on cat.parentid = hierarchycte.id)
select * from hierarchycte