USP_MKTSEGMENTATIONEXPORTPROCESS_GETSEGMENTCHUNKS

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_GETSEGMENTCHUNKS]
(
  @CHUNKTABLENAME nvarchar(255)
)
as
  set nocount on;

  declare @SQL nvarchar(max);

  set @SQL =
    'select' + char(13) +
    '  [CHUNK].[ID],' + char(13) +
    '  [CHUNK].[SOURCEQUERYVIEWCATALOGID],' + char(13) +
    '  [QUERYVIEWCATALOG].[OBJECTNAME] as [SOURCEQUERYVIEWOBJECTNAME],' + char(13) +
    '  [QUERYVIEWCATALOG].[QUERYVIEWSPEC] as [SOURCEQUERYVIEWXML],' + char(13) +
    '  [CHUNK].[MARKETINGRECORDTYPECODE],' + char(13) +
    '  [CHUNK].[USEADDRESSPROCESSING],' + char(13) +
    '  [CHUNK].[ADDRESSPROCESSINGOPTIONID],' + char(13) +
    '  [CHUNK].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],' + char(13) +
    '  [CHUNK].[NAMEFORMATPARAMETERID],' + char(13) +
    '  isnull([NAMEFORMATPARAMETER].[JOINTEXCLUDESPOUSE], 0),' + char(13) +
    '  isnull([NAMEFORMATPARAMETER].[JOINTSPOUSESMAILEDSEPARATELY], 0),' + char(13) +
    '  cast (case [CHUNK].[MARKETINGRECORDTYPECODE] when 1 then 1 when 2 then 0 when 3 then 1 end as bit) as [CONSTITUENTS]' + char(13) +
    'from [' + @CHUNKTABLENAME + '] as [CHUNK]' + char(13) +
    'inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [CHUNK].[SOURCEQUERYVIEWCATALOGID]' + char(13) +
    'left outer join dbo.[NAMEFORMATPARAMETER] on [NAMEFORMATPARAMETER].[ID] = [CHUNK].[NAMEFORMATPARAMETERID]' + char(13) +
    'union all' + char(13) +
    'select' + char(13) +
    '  [CHUNK].[ID],' + char(13) +
    '  [CHUNK].[SOURCEQUERYVIEWCATALOGID],' + char(13) +
    '  [QUERYVIEWCATALOG].[OBJECTNAME] as [SOURCEQUERYVIEWOBJECTNAME],' + char(13) +
    '  [QUERYVIEWCATALOG].[QUERYVIEWSPEC] as [SOURCEQUERYVIEWXML],' + char(13) +
    '  [CHUNK].[MARKETINGRECORDTYPECODE],' + char(13) +
    '  [CHUNK].[USEADDRESSPROCESSING],' + char(13) +
    '  [CHUNK].[ADDRESSPROCESSINGOPTIONID],' + char(13) +
    '  [CHUNK].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],' + char(13) +
    '  [CHUNK].[NAMEFORMATPARAMETERID],' + char(13) +
    '  isnull([NAMEFORMATPARAMETER].[JOINTEXCLUDESPOUSE], 0),' + char(13) +
    '  isnull([NAMEFORMATPARAMETER].[JOINTSPOUSESMAILEDSEPARATELY], 0),' + char(13) +
    '  cast (0 as bit) as [CONSTITUENTS]' + char(13) +
    'from [' + @CHUNKTABLENAME + '] as [CHUNK]' + char(13) +
    'inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [CHUNK].[SOURCEQUERYVIEWCATALOGID]' + char(13) +
    'left outer join dbo.[NAMEFORMATPARAMETER] on [NAMEFORMATPARAMETER].[ID] = [CHUNK].[NAMEFORMATPARAMETERID]' + char(13) +
    'where [CHUNK].[MARKETINGRECORDTYPECODE] = 3';

  -- this will generate a second "chunk" for consolidated list segments: one for the constituents (to whom address processing will be applied)

  -- and one for the list members (to whom address processing will not be applied)


  exec (@SQL);

--  declare @SPWRAP table ([ID] uniqueidentifier not null, [SOURCEQUERYVIEWCATALOGID] uniqueidentifier not null, [SOURCEQUERYVIEWOBJECTNAME] nvarchar(255) not null, [SOURCEQUERYVIEWXML] xml not null, [MARKETINGRECORDTYPECODE] tinyint not null, [USEADDRESSPROCESSING] bit not null, [ADDRESSPROCESSINGOPTIONID] uniqueidentifier not null, [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] datetime not null, [NAMEFORMATPARAMETERID] uniqueidentifier not null, [JOINTEXCLUDESPOUSE] bit not null, [JOINTSPOUSESMAILEDSEPARATELY] bit not null, [CONSTITUENTS] bit not null)

--  select * from @SPWRAP;


  return 0;