Firstly, have a look at the output of this query :
declare @nodeid int
set @nodeid = 109;
with rootCTE(id, parentid, level )
as
(
select id, parent ,1
from table
where parent = @nodeid
union all
select e.id, e.parent , r.level+1 from table t, rootCTE r where t.parent = r.id
)
select * from rootCTE
Then to get the count of children up to level 2 run this one :
declare @nodeid int
set @nodeid = 109;
with rootCTE(id, parentid, level )
as
(
select id, parent ,1
from table
where parent = @nodeid
union all
select e.id, e.parent , r.level+1 from table t, rootCTE r where t.parent = r.id
)
select COUNT(*) from rootCTE
where level<=2
To count all children remove the where clause.