declare @mylevel int;
CREATE TABLE #temp
(
usb_recommend_id NVARCHAR(20),
usb_us_id NVARCHAR(20),
path1 NVARCHAR(MAX)
);
WITH TEST_CTE 
AS
(
SELECT TBIE.usb_recommend_id,TBIE.usb_us_id,1 as mylevel,CAST(TBIE.usb_us_id as nvarchar(4000)) AS PATH1
FROM us_userInfo TBIE
WHERE TBIE.usb_us_id = 30
UNION ALL
SELECT CTBIE.usb_recommend_id,CTBIE.usb_us_id,CTE.mylevel+1,CTE.PATH1+'>'+Cast(CTBIE.usb_us_id as nvarchar(4000)) PATH1 
FROM us_userInfo CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.usb_recommend_id=CTE.usb_us_id and CTE.mylevel < 100
)
--select * into #table_Name from TEST_CTE

insert into #temp (usb_recommend_id,usb_us_id,path1) select usb_recommend_id,usb_us_id,path1 FROM TEST_CTE OPTION(MAXRECURSION 100)
--if @mylevel < 10 BEGIN
-- return @mylevel
-- END
-- RETURN NULL
--SELECT * FROM #temp 
SELECT TOP 1 usb_us_id, len(path1) - len(replace(path1,'>','')) c_count,
--SUBSTRING(path1,charindex('>',path1)+1,len(path1)) 
SUBSTRING(
substring(path1,charindex('>',path1)+1,len(path1)-charindex('>',path1)),0,CHARINDEX('>',substring(path1,charindex('>',path1)+1,len(path1)-charindex('>',path1)))) AS mychild
FROM #temp ORDER BY c_count DESC

DELETE FROM #temp
DROP TABLE #temp