CREATE PROCEDURE [dbo].[GetGroupInfo]

    @s_code NVARCHAR() =   --会员卡号

AS

BEGIN

declare @p int;                    --查询唯一性结果

declare @sql nvarchar();       --拼接查询sql字串

set @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::Parse('/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) as varchar()) + '/' as hierarchyid)

        ,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<>- and a.n_user> and a.n_teamlevel < @p

)

--SELECT * FROM cte WHERE n_teamlevel < @p and s_code <> @s_code and n_teamlevel <> null;

SELECT cast(NodeID as hierarchyid).ToString(),n_user,s_code,s_introducer,n_teamlevel FROM cte

END

---execute [dbo].[GetGroupInfo] ''