USP_EMAIL_GETCHUNKEDMERGEDATA

      CREATE procedure [dbo].[USP_EMAIL_GETCHUNKEDMERGEDATA]
       (
        @QUEUEID uniqueidentifier,
        @ISFIRSTCHUNK bit,
        @INSTANCEID uniqueidentifier,
        @PROCESSINGID uniqueidentifier,
        @TOTALMERGEROWS integer,
        @AREALLEMAILSBLACKLISTED bit = 0 output
      )
      with execute as owner
      as

      begin

        set nocount on;

        declare @GETCHUNKDATASQL nvarchar(4000)
        declare @TABLENAME nvarchar(255)
        declare @UNIQUEIDFIELDNAME nvarchar(128)
        declare @EMAILADDRESSFIELDNAME nvarchar(128)
        declare @MERGEDATAOBJECT nvarchar(256)

        begin try

        select @UNIQUEIDFIELDNAME = UNIQUEIDFIELDNAME, @EMAILADDRESSFIELDNAME = EMAILADDRESSFIELDNAME from dbo.EMAILSTATUSGENERALPURPOSE where EMAILQUEUEGENERALPURPOSEID = @QUEUEID;

        --If the viewname is null this job was passed over with a dataset instead of a view. A table was created prior to this step to hold the dataset merge data

        select @MERGEDATAOBJECT = 
          case 
                  when ISNULL(SQLVIEWCATALOG.VIEWNAME,'00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' then dbo.UFN_GENERALPURPOSEEMAIL_GETMERGEDATASETTABLENAME(@QUEUEID) 
                  else 'dbo.' + SQLVIEWCATALOG.VIEWNAME 
              end 
            from EMAILQUEUEGENERALPURPOSE left join SQLVIEWCATALOG on EMAILQUEUEGENERALPURPOSE.MERGESQLVIEWID = SQLVIEWCATALOG.ID where EMAILQUEUEGENERALPURPOSE.ID = @QUEUEID;

        set @TABLENAME = dbo.UFN_GENERALPURPOSEEMAIL_GETDATATABLENAME(@QUEUEID)

        set @GETCHUNKDATASQL = 'set nocount on; 
        if object_id(''' + @MERGEDATAOBJECT + ''') is not null
        begin
              -- create and populate recipient table on first chunk

                  if ' + cast(@ISFIRSTCHUNK as nvarchar(10)) + ' = 1 and object_id(''' + @TABLENAME + ''') is null
                    begin
                        --if object_id(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME + ';


                        create table ' + @TABLENAME + ' (MERGEROWID uniqueidentifier not null, INSTANCEID uniqueidentifier not null, RECIPIENTID integer not null identity (1, 1));

                        declare @MERGEIDS table
                        (
                            MERGEROWID uniqueidentifier
                        )

                        insert into ' + @TABLENAME + ' (MERGEROWID, INSTANCEID) output inserted.MERGEROWID into @MERGEIDS select [' + @UNIQUEIDFIELDNAME + '], ''' + cast(@INSTANCEID as nvarchar(36)) + ''' from ' + @MERGEDATAOBJECT + ' where not exists (select 1 from dbo.EMAILINVALIDRECIPIENT where ADDRESS = [' + @EMAILADDRESSFIELDNAME + '] and ISBLACKLISTED = 1) order by [' + @UNIQUEIDFIELDNAME + '];      

            create nonclustered index IX_' + right(@TABLENAME,len(@TABLENAME) - 4) + '_MERGEROWID on ' + @TABLENAME + '
            (
                MERGEROWID asc
            );

            if (select top 1 1 from @MERGEIDS) is null
            begin
              set @AREALLEMAILSBLACKLISTEDOUT = 1;
              -- drop table

              if object_id(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME + ';
            end

                    end
          else
          begin
                  update ' + @TABLENAME + ' set INSTANCEID = ''' + cast(@INSTANCEID as nvarchar(36)) + '''
              where INSTANCEID = ''00000000-0000-0000-0000-000000000000'';
          end

          if @AREALLEMAILSBLACKLISTEDOUT = 0
          begin
            -- get data from table (invalid accounts should never make it to the table now that we filter the inserted rows)

                    select MD.*, P.RECIPIENTID TEMPRECIPID001 from ' + @MERGEDATAOBJECT + ' MD inner join 
                    ' + @TABLENAME + ' P on MD.[' + @UNIQUEIDFIELDNAME + '] = P.MERGEROWID and P.INSTANCEID = ''' + cast(@INSTANCEID as nvarchar(36)) + ''' ;
                  end                      
        end'

      declare @PARAMDEFINITION nvarchar(500) = N'@AREALLEMAILSBLACKLISTEDOUT bit = 0 output';
            exec sp_executesql @GETCHUNKDATASQL, @PARAMDEFINITION, @AREALLEMAILSBLACKLISTEDOUT = @AREALLEMAILSBLACKLISTED output;

      end try
        begin catch
            exec dbo.USP_RAISE_ERROR
            return 1
        end catch
      end