USE [test]

 GO

 /****** Object:  StoredProcedure [dbo].[p02_get_group_member]    Script Date: 2019/7/8 14:58:03 ******/

 SET ANSI_NULLS ON

 GO

 SET QUOTED_IDENTIFIER ON

 GO

 ALTER PROC [dbo].[p02_get_group_member]

 AS

 --s2，提取s1中担保圈路径中的成员

 BEGIN

     IF OBJECT_ID(N'dbo.t02_get_group_member') IS NOT NULL

         DROP TABLE dbo.t02_get_group_member;

     SELECT b.id,

            b.cchar AS cust_name,

            a.groupno,

            a.nlevel

     INTO dbo.t02_get_group_member --将循环链条的拆分结果输出到表中

     FROM dbo.t01_get_group_path AS a

         CROSS APPLY dbo.tf_split_char(a.full_path, '<') AS b;

     --对上面的表建立索引

     ALTER TABLE [dbo].t02_get_group_member

     ALTER COLUMN [groupno] BIGINT NOT NULL;

     ALTER TABLE [dbo].t02_get_group_member ADD PRIMARY KEY (groupno, id);

     ALTER TABLE [dbo].t02_get_group_member

     ALTER COLUMN [cust_name] NVARCHAR(100) NOT NULL;

     ALTER TABLE [dbo].t02_get_group_member ALTER COLUMN nlevel INT NOT NULL;

     --建立名称索引

     CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190523-113242]

     ON [dbo].t02_get_group_member ([cust_name] ASC)

     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,

           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

          );

 END;