USP_DATAFORMTEMPLATE_ADD_RESEARCHLISTSPLITBATCH

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RESEARCHLISTSPLITBATCH
(
    @ID uniqueidentifier = null output,
        @CHANGEAGENTID uniqueidentifier = null,
        @CURRENTAPPUSERID uniqueidentifier,
      @CONTEXTID uniqueidentifier,
      @BATCHSIZE integer = 1
)

  as begin
  set nocount on;

  --Begin work

  begin try
      begin transaction;

      declare @PREFIX nvarchar(50) = 'Subquery: ';
      declare @RESEARCHGROUPNAME nvarchar(50);
      declare @CHILDPREFIX nvarchar(100);
          declare @MAXSCREEN int;    -- RP search limit, larger searches may not return

          declare @RGCOUNT integer;
          declare @COUNT integer;

          select @RESEARCHGROUPNAME = NAME from dbo.RESEARCHGROUP where @CONTEXTID = ID
          set @CHILDPREFIX = @PREFIX + @RESEARCHGROUPNAME;
          if exists(select top 1 1 from dbo.RESEARCHGROUP where NAME like @CHILDPREFIX + '%')
            raiserror('Child lists already exist for research list ''%s''.', 13, 1, @RESEARCHGROUPNAME);

      set @MAXSCREEN = @BATCHSIZE;

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

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

          declare @NUMMEMBERS integer = 0;

          select @NUMMEMBERS = COUNT(*)
          from 
            RESEARCHGROUPMEMBER 
          left join
            WEALTH on WEALTH.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
          where 
            RESEARCHGROUPMEMBER.RESEARCHGROUPID = @CONTEXTID

      if @MAXSCREEN >= @NUMMEMBERS
            raiserror('Research list is already smaller than the split size of %i.', 13, 1, @MAXSCREEN);

          set @RGCOUNT = @NUMMEMBERS / @MAXSCREEN;
      if (@NUMMEMBERS % @MAXSCREEN) > 0
        set @RGCOUNT = @RGCOUNT + 1;

          select @COUNT = 1
          while @COUNT <= @RGCOUNT
          begin

            insert into dbo.RESEARCHGROUP (
                    NAME,
                    DESCRIPTION,
                  NUMMEMBERS,
                    OTHERSCANVIEW,
                    OTHERSCANMODIFY,
                    ADMINONLY,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
            )
                select
                    @CHILDPREFIX + ' ' + CAST(@COUNT as varchar(20)) + ' of ' + CAST(@RGCOUNT as varchar(20)),
                    @CHILDPREFIX,
                    0,
                    0,
                    0,
                    0,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE

          select @COUNT = @COUNT + 1
          end;

          with MEMBER_CTE as (
              select 
                  ROW_NUMBER() OVER(ORDER BY C.NAME) AS Row, RGM.CONSTITUENTID
              from RESEARCHGROUPMEMBER RGM
              inner join CONSTITUENT C on C.ID = RGM.CONSTITUENTID
              where RGM.RESEARCHGROUPID = @CONTEXTID
          )
          insert into dbo.RESEARCHGROUPMEMBER (
            RESEARCHGROUPID,
            CONSTITUENTID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          select 
            RG.RESEARHGROUPID,
            MEMBER_CTE.CONSTITUENTID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          from MEMBER_CTE
          inner join (
          select 
              NTILE(@RGCOUNT) OVER(ORDER BY NAME) SEQUENCE,
              ID RESEARHGROUPID
          from RESEARCHGROUP where NAME like @CHILDPREFIX + '%'
          ) RG 
          on RG.SEQUENCE = ((MEMBER_CTE.ROW - 1)/@MAXSCREEN + 1);

          update
              dbo.RESEARCHGROUP
          set 
              NUMMEMBERS = (select count(ID) from dbo.RESEARCHGROUPMEMBER where RESEARCHGROUPID = RESEARCHGROUP.ID)
          where NAME like @CHILDPREFIX + '%';

      commit transaction;

      return 0;

  end try

  begin catch
      rollback transaction;
      exec dbo.USP_RAISE_ERROR;

  end catch

  end

return 0