Recursion in SQL Server function with idle cursor

Question

This is a table structure.

select nid, memberid, sponsorid 
from tblmember;

Which gives the following result:

nid memerid sponsor
-------------------
1   679414  0
2   622411  679414
3   647964  679414
5   285631  679414
6   119979  

I am trying to create a tree-like structure where each node can only have three children, if a member with memberid 679414introduces a new branch, and it already has three children, then a new branch will be added for the first child of the node 679414, if it has less 3 child nodes. The sponsor is the parent nodeid member (which is unique). To find the first node tree in 679414which has less than 3 child nodes, I created the following function with the cursor.

ALTER FUNCTION dbo.getSponsor (@id VARCHAR(50))
RETURNS VARCHAR(101)
AS
BEGIN
    DECLARE @cou INT;
    DECLARE @id1 VARCHAR(200);

    SELECT @cou = count(*)
    FROM tblmember
    WHERE sponsorid = @id

    IF (@cou < 3)
    BEGIN
        RETURN @id
    END
    ELSE
    BEGIN
        DECLARE db_cursor LOCAL FOR
            SELECT memberid
            FROM dbo.tblmember
            WHERE sponsorid = @id

        OPEN db_cursor

        FETCH NEXT FROM db_cursor INTO @id1

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC getsponsor @id1;
        END

        CLOSE db_cursor
        DEALLOCATE db_cursor
    END

    RETURN '';
END

But this cursor is not executed when I try to use it using the following code.

DECLARE @a VARCHAR(200);
EXEC @a = [getSponsor] '679414'
SELECT @a;

-, ? , , , Local .

Answer 1

. node .

;WITH CTE AS (
    SELECT *, 1 R FROM tblmember T WHERE sponsorid = @id
    UNION ALL 
    SELECT T.id, T.memberid, T.sponsorid, R + 1 R FROM tblmember T INNER JOIN CTE ON T.sponsorid = CTE.memberid
)
,UsedNodes AS (
    SELECT sponsorid, R FROM CTE 
    GROUP BY sponsorid, R
    HAVING COUNT(*) = 3
)
SELECT TOP 1 @return_id = memberid 
FROM CTE 
WHERE memberid NOT IN ( SELECT sponsorid FROM UsedNodes )
ORDER BY R, id