CTE Function in SQL For Search Child Nodes

FIRST METHOD
========================================

CREATE proc spGetAllMembers(@MemberID bigint)
AS
begin
WITH emptree(uid, name, ref_id) AS (
    SELECT uid,
      name,
           ref_id
         
    FROM register
    WHERE ref_id = @MemberID

    UNION ALL

    SELECT c.uid,
           c.name,
           c.ref_id
    FROM register c
       JOIN emptree p ON p.uid = c.ref_id
)
SELECT *
FROM emptree

end
GO


execute spGetAllMembers 2000

SECOND METHOD
=======================================

CREATE FUNCTION findno(@nodeid bigint)
    RETURNS INT
    AS
    BEGIN
   

declare @result int;
;with cte as (
       select
               uid, ref_id, lleg,mleg,rleg,
               null node
           
       from register where uid = @nodeid
       union all
       select
               t.uid, t.ref_id, t.lleg,t.mleg,t.rleg,
               ISNULL(cte.node, CASE WHEN t.lleg = NULL THEN 0 ELSE 1 END) lnode
             
       from register t
       inner join cte
               on cte.uid = t.ref_id
)
select
   
      @result= SUM(node)+1
     
from cte
RETURN @result;

END

------------------------------------
For executions

SELECT dbo.findno(2013) AS node