USP_BBAMODELINGANDPROPENSITYFILEIMPORT_CREATEAUXILIARYRESEARCHGROUPS

            CREATE procedure dbo.USP_BBAMODELINGANDPROPENSITYFILEIMPORT_CREATEAUXILIARYRESEARCHGROUPS (
                @RESEARCHGROUPNAME nvarchar(100),
                @RESEARCHGROUPDESCRIPTION nvarchar(255) = '',
                @AUXILIARYRESEARCHGROUPSIZE integer = 2500,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier = null
            ) as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETCHANGEAGENT @CHANGEAGENTID output;

                begin try
                    declare @contextCache varbinary(128);

                    --cache current context information

                    set @contextCache = CONTEXT_INFO();

                    --set CONTEXT_INFO to @CHANGEAGENTID

                    set CONTEXT_INFO @CHANGEAGENTID;

                    delete
                        dbo.RESEARCHGROUP
                    from
                        dbo.RESEARCHGROUP RG
                    where
                        RG.NAME <> @RESEARCHGROUPNAME and RG.NAME like @RESEARCHGROUPNAME+ '%';

                    declare @AUXILIARYRESEARCHGROUPSTOCREATE integer;
                    select
                        @AUXILIARYRESEARCHGROUPSTOCREATE = ceiling(cast(count(RGM.ID) as float) / cast(@AUXILIARYRESEARCHGROUPSIZE as float))
                    from dbo.RESEARCHGROUPMEMBER RGM
                    inner join dbo.RESEARCHGROUP RG on RG.ID = RGM.RESEARCHGROUPID
                    where RG.NAME = @RESEARCHGROUPNAME;

                    if @AUXILIARYRESEARCHGROUPSTOCREATE > 0 begin                        
                        declare @CURRENTAUXILIARYRESEARCHGROUPNUMBER int;
                        set @CURRENTAUXILIARYRESEARCHGROUPNUMBER = 1;
                        while @CURRENTAUXILIARYRESEARCHGROUPNUMBER <= @AUXILIARYRESEARCHGROUPSTOCREATE begin
                            insert into dbo.RESEARCHGROUP (
                                NAME,
                                DESCRIPTION,
                                OWNERID,
                OTHERSCANVIEW,
                                OTHERSCANMODIFY,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED,
                                ADMINONLY
                            )
                            values (
                                @RESEARCHGROUPNAME + ' ' + cast(@CURRENTAUXILIARYRESEARCHGROUPNUMBER as nvarchar(20)),
                                @RESEARCHGROUPDESCRIPTION,
                                @CURRENTAPPUSERID,
                                0,
                0,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                                1
                            );

                            set @CURRENTAUXILIARYRESEARCHGROUPNUMBER = @CURRENTAUXILIARYRESEARCHGROUPNUMBER + 1;
                        end;

                        with AUXILIARYRESEARCHGROUPFORCONSTITUENT_CTE as (
                            select
                                ceiling(cast(row_number() over (order by RGM.ID desc) as float) / cast(@AUXILIARYRESEARCHGROUPSIZE as float)) AUXILIARYRESEARCHGROUPNUMBER,
                                RGM.CONSTITUENTID CONSTITUENTID
                            from dbo.RESEARCHGROUPMEMBER RGM
                            inner join dbo.RESEARCHGROUP RG on RG.ID = RGM.RESEARCHGROUPID
                            where RG.NAME = @RESEARCHGROUPNAME
                        )
                        insert into dbo.RESEARCHGROUPMEMBER (
                            RESEARCHGROUPID,
                            CONSTITUENTID,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        select
                            RG.ID,
                            CONSTITUENTID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        from AUXILIARYRESEARCHGROUPFORCONSTITUENT_CTE
                        left join dbo.RESEARCHGROUP RG on RG.NAME = @RESEARCHGROUPNAME + ' ' + cast(AUXILIARYRESEARCHGROUPNUMBER as nvarchar(20));

                        select
                            RG.ID,
                            COUNT(RGM.ID)
                        from dbo.RESEARCHGROUP RG
                        left join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                        where RG.NAME like @RESEARCHGROUPNAME + '%'
                        group by RG.ID;

                        set @CURRENTAUXILIARYRESEARCHGROUPNUMBER = 1;
                        declare @CURRENTRESEARCHGROUPID uniqueidentifier;
                        while @CURRENTAUXILIARYRESEARCHGROUPNUMBER <= @AUXILIARYRESEARCHGROUPSTOCREATE begin
                            select @CURRENTRESEARCHGROUPID = RG.ID from dbo.RESEARCHGROUP RG where RG.NAME = @RESEARCHGROUPNAME + ' ' + cast(@CURRENTAUXILIARYRESEARCHGROUPNUMBER as nvarchar(20));
                            exec dbo.USP_RESEARCHGROUP_RECALCULATENUMMEMBERS @CURRENTRESEARCHGROUPID, @CHANGEAGENTID;
                            set @CURRENTAUXILIARYRESEARCHGROUPNUMBER = @CURRENTAUXILIARYRESEARCHGROUPNUMBER + 1;
                        end;
                    end

                    --reset CONTEXT_INFO to previous value

                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache;
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;