ALTER proc [dbo].[p_multipleChoiceSplit]
as
declare @Code varchar(50),@s_chronic varchar(100),@s_CHSISRequire varchar(100),@s_HealthCheckProblem varchar(100)
---记录数组的长度
declare @length int,@next int
---对表2B-7，3A-4,3B-3b多选项进行拆分
declare cur_list cursor for select Code,IsHaveChronic,CHSISRequire,HealthCheckProblem from dbo.PeopleQuestion where IsHaveChronic!='' or CHSISRequire!='' or HealthCheckProblem!=''
begin
    ---建立拆分的多选项表
    if not exists(select name from sys.tables where name='t_multipleChoiceSplit' and type='u')
        create table t_multipleChoiceSplit(Code varchar(50) null,[Type] varchar(50) null ,[Option] varchar(50) null)
    open cur_list
    fetch next from cur_list into @Code,@s_chronic,@s_CHSISRequire,@s_HealthCheckProblem
    while @@fetch_status=0
    begin
        ---2B-7
        
        if (@s_chronic!='')
        begin
            set @length=dbo.Get_StrArrayLength(@s_chronic,'.')
            set @next=1
            if not exists(select Code from t_multipleChoiceSplit where Code=@Code and [Type]='IsHaveChronic')
            begin
                while @next<=@length
                begin
                    insert into t_multipleChoiceSplit values(@Code,'IsHaveChronic',dbo.Get_StrArrayStrOfIndex(@s_chronic,'.',@next))
                    set @next=@next+1
                end
            end
        end

        ----3A-4
         if (@s_CHSISRequire!='')
        begin
            set @length=dbo.Get_StrArrayLength(@s_CHSISRequire,'.')
            set @next=1
            if not exists(select Code from t_multipleChoiceSplit where Code=@Code and [Type]='CHSISRequire')
            begin
                while @next<=@length
                begin
                    insert into t_multipleChoiceSplit values(@Code,'CHSISRequire',dbo.Get_StrArrayStrOfIndex(@s_CHSISRequire,'.',@next))
                    set @next=@next+1
                end
            end
        end

        ----3B-3b
        if(@s_HealthCheckProblem!='')
        begin
            set @length=dbo.Get_StrArrayLength(@s_HealthCheckProblem,'.')
            set @next=1
            if not exists(select Code from t_multipleChoiceSplit where Code=@Code and [Type]='HealthCheckProblem')
            begin
                while @next<=@length
                begin
                    insert into t_multipleChoiceSplit values(@Code,'HealthCheckProblem',dbo.Get_StrArrayStrOfIndex(@s_HealthCheckProblem,'.',@next))
                    set @next=@next+1
                end
            end
        end

        fetch next from cur_list into @Code,@s_chronic,@s_CHSISRequire,@s_HealthCheckProblem
    end
    select distinct * into #t_multipleChoiceSplit from t_multipleChoiceSplit
    delete from t_multipleChoiceSplit
    insert into t_multipleChoiceSplit select * from #t_multipleChoiceSplit
    close cur_list
    deallocate cur_list
end