SQL Server stored procedure recursive query grouping +hierarchyid rebuilding membership relationship

CREATE PROCEDURE [dbo]. [GetGroupInfo] @s_code NVARCHAR ( -) =0--card number Asbegindeclare @pint; --Query Uniqueness Results declare @sql nvarchar ( +); --splicing Query SQL stringSet@sql ='SELECT @p=n_teamlevel from T_user_basic WHERE s_code='+@s_code; exec sp_executesql @sql, N'@p as int OUTPUT', @p output;with CTE (nodeid,n_user,s_code,s_introducer,n_teamlevel) as(SELECT NodeID=hierarchyid::P Arse ('/1/'), N_user, S_code, S_introducer, n_teamlevel from T_user_basic WHERE s_code=@s_code UNION ALL SELECT NodeID=cast (b.nodeid.tostring () + cast (Row_number () over (order by A.n_user) asvarcharTen)) +'/'  asHierarchyID), A.n_user, A.s_code, A.s_introducer, a.n_teamlevel from T_user_basic a INNER JOIN CTE B on (A.s_introducer=B.s_code) WHERE A.s_introducer<>-1and a.n_user>0< A.n_teamlevel@p)--select * from CTE WHERE N_teamlevel < @p and S_code <> @s_code and N_teamlevel <>NULL; SELECT Cast (NodeID asHierarchyID). ToString (), n_user,s_code,s_introducer,n_teamlevel from Cteend---execute [dbo]. [GetGroupInfo]'864160951'

SQL Server stored procedure recursive query grouping +hierarchyid rebuilding membership relationship