USP_DATALIST_MKTSEGMENTATIONSEGMENT_MEMBER

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTATIONSEGMENT_MEMBER]
(
  @MAILINGSEGMENTID uniqueidentifier,
  @TOTALROWS int = 500,
  @FILTER nvarchar(253) = null
)
as
  set nocount on;

  declare @PARENTTABLENAME nvarchar(255); 
  declare @PARENTID nvarchar(255); 
  declare @SEGMENTDONORSTABLENAME nvarchar(255); 
  declare @RECORDSOURCEID uniqueidentifier;
  declare @RECORDSOURCETYPE as tinyint;
  declare @ISTESTSEGMENT bit;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @USEADDRESSCACHE bit;
  declare @USEEMAILADDRESSCACHE bit;
  declare @MAILINGTYPECODE tinyint;
  declare @SEGMENTTYPECODE tinyint;
  declare @CHANNELCODE tinyint;
  declare @USEADDRESSPROCESSING bit;
  declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @ADDRESSCACHETABLENAME nvarchar(128);
  declare @EMAILADDRESSCACHETABLENAME nvarchar(128);
  declare @FIXEDWIDTH nvarchar(2);
  declare @ISBBEC bit;
  declare @DONORIDSQL nvarchar(128);

  begin try
    if not exists(select [ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @MAILINGSEGMENTID)
      select
        @ISTESTSEGMENT = 0,
        @SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
        @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        @RECORDSOURCETYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENTATIONSEGMENT].[SEGMENTID]),
        @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
        @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
        @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
        @CHANNELCODE = isnull([MKTPACKAGE].[CHANNELCODE], 255),
        @USEADDRESSPROCESSING = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] else [MKTSEGMENTATION].[USEADDRESSPROCESSING] end,
        @ADDRESSPROCESSINGOPTIONID = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID] end,
        @FIXEDWIDTH = convert(nvarchar(2), dbo.[UFN_MKTFINDERNUMBER_GETFIXEDWIDTH](1, [MKTSEGMENTATION].[ID])),
        @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID])
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @MAILINGSEGMENTID;
    else
      select
        @ISTESTSEGMENT = 1,
        @SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
        @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        @RECORDSOURCETYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENTATIONSEGMENT].[SEGMENTID]),
        @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
        @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
        @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
        @CHANNELCODE = isnull([MKTPACKAGE].[CHANNELCODE], 255),
        @USEADDRESSPROCESSING = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] else [MKTSEGMENTATION].[USEADDRESSPROCESSING] end,
        @ADDRESSPROCESSINGOPTIONID = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID] end,
        @FIXEDWIDTH = convert( nvarchar(2), dbo.[UFN_MKTFINDERNUMBER_GETFIXEDWIDTH](1, [MKTSEGMENTATION].[ID])),
        @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID])
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = (select [SEGMENTID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @MAILINGSEGMENTID);

    set @SEGMENTDONORSTABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

    --make sure table exists

    if not exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = @SEGMENTDONORSTABLENAME)
      exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CREATEDATATABLE] @SEGMENTATIONID;

    if @RECORDSOURCETYPE = 1  --Record Source

      select 
        @PARENTTABLENAME = Q.[OBJECTNAME],
        @PARENTID = Q.[PRIMARYKEYFIELD]
      from dbo.[MKTSEGMENT] S
      inner join dbo.[QUERYVIEWCATALOG] Q on Q.[ID] = S.[QUERYVIEWCATALOGID]
      where S.[ID] = @SEGMENTID;
    else
      begin
        if @RECORDSOURCETYPE = 2  --List or duplicate list

          select
            @PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
            @PARENTID = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD]
          from dbo.[MKTSEGMENT]
          inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
          left join dbo.[MKTSEGMENT] as [PARENTSEGMENT] on [PARENTSEGMENT].[ID] = [MKTSEGMENTLIST].[PARENTSEGMENTID]
          left join dbo.[MKTSEGMENTLIST] as [PARENTSEGMENTLIST] on [PARENTSEGMENTLIST].[ID] = [PARENTSEGMENT].[CURRENTSEGMENTLISTID]
          inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] or [QUERYVIEWCATALOG].[ID] = [PARENTSEGMENTLIST].[QUERYVIEWCATALOGID]
          where [MKTSEGMENT].[ID] = @SEGMENTID;
        else
          begin
            if @RECORDSOURCETYPE = 3  --Consolidated

              select 
                @PARENTTABLENAME = CQ.[OBJECTNAME],
                @PARENTID = CQ.[PRIMARYKEYFIELD]
              from dbo.[MKTSEGMENT] S
              inner join dbo.[QUERYVIEWCATALOG] Q on Q.[ID] = S.[QUERYVIEWCATALOGID]
              inner join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] C on C.[ID] = Q.[ID]
              inner join dbo.[QUERYVIEWCATALOG] CQ on CQ.[ID] = C.[CONSOLIDATEDQUERYVIEWCATALOGID]
              where S.[ID] = @SEGMENTID
            else
              raiserror('Segment contains an invalid selection type.', 13, 1);
          end
      end

    /* Only use address cache for BBEC only, appeal efforts, constituent segments, and mail channel code. */
    set @ADDRESSCACHETABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
    if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @ADDRESSCACHETABLENAME) and
       @ISBBEC = 1 and ((@MAILINGTYPECODE = 0 and @SEGMENTTYPECODE = 1) or (@MAILINGTYPECODE = 5 and @SEGMENTTYPECODE = 3)) and @CHANNELCODE = 0 and @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
      set @USEADDRESSCACHE = 1;
    else
      set @USEADDRESSCACHE = 0;

    set @EMAILADDRESSCACHETABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
    if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @EMAILADDRESSCACHETABLENAME) and
       @ISBBEC = 1 and ((@MAILINGTYPECODE = 0 and @SEGMENTTYPECODE = 1) or (@MAILINGTYPECODE = 5 and @SEGMENTTYPECODE = 3)) and @CHANNELCODE = 1 and @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
      set @USEEMAILADDRESSCACHE = 1;
    else
      set @USEEMAILADDRESSCACHE = 0;

    -- these represent the mapped fields --

    declare @FULLNAMEFIELDNAME nvarchar(255);
    declare @FIRSTNAMEFIELDNAME nvarchar(255);
    declare @MIDDLENAMEFIELDNAME nvarchar(255);
    declare @LASTNAMEFIELDNAME nvarchar(255);
    declare @TITLEFIELDNAME nvarchar(255);
    declare @SUFFIXFIELDNAME nvarchar(255);
    declare @COUNTRYFIELDNAME nvarchar(255);
    declare @ADDRESSLINE1FIELDNAME nvarchar(255);
    declare @ADDRESSLINE2FIELDNAME nvarchar(255);
    declare @CITYFIELDNAME nvarchar(255);
    declare @STATEFIELDNAME nvarchar(255);
    declare @POSTCODEFIELDNAME nvarchar(255);
    declare @CARTFIELDNAME nvarchar(255);
    declare @DPCFIELDNAME nvarchar(255);
    declare @LOTFIELDNAME nvarchar(255);
    declare @PHONENUMBERFIELDNAME nvarchar(255);
    declare @EMAILADDRESSFIELDNAME nvarchar(255);
    declare @FIRSTGIFTDATEFIELDNAME nvarchar(255);
    declare @ADDRESSLINE3FIELDNAME nvarchar(255);
    declare @ADDRESSLINE4FIELDNAME nvarchar(255);
    declare @ADDRESSLINE5FIELDNAME nvarchar(255);
    declare @LOOKUPIDFIELDNAME nvarchar(255);

    -- grab the field names --

    select
      @FULLNAMEFIELDNAME = [FULLNAMEFIELD] 
      ,@FIRSTNAMEFIELDNAME = [FIRSTNAMEFIELD]
      ,@MIDDLENAMEFIELDNAME = [MIDDLENAMEFIELD]
      ,@LASTNAMEFIELDNAME = [LASTNAMEFIELD]
      ,@TITLEFIELDNAME = [TITLEFIELD]
      ,@SUFFIXFIELDNAME = [SUFFIXFIELD]
      ,@COUNTRYFIELDNAME = [COUNTRYFIELD]
      ,@ADDRESSLINE1FIELDNAME = [ADDRESSLINE1FIELD]
      ,@ADDRESSLINE2FIELDNAME = [ADDRESSLINE2FIELD]
      ,@CITYFIELDNAME = [CITYFIELD]
      ,@STATEFIELDNAME = [STATEFIELD]
      ,@POSTCODEFIELDNAME = [POSTCODEFIELD]
      ,@CARTFIELDNAME = [CARTFIELD]
      ,@DPCFIELDNAME = [DPCFIELD]
      ,@LOTFIELDNAME = [LOTFIELD]
      ,@PHONENUMBERFIELDNAME = [PHONENUMBERFIELD]
      ,@EMAILADDRESSFIELDNAME = [EMAILADDRESSFIELD]
      ,@FIRSTGIFTDATEFIELDNAME = [FIRSTGIFTDATEFIELD]
      ,@ADDRESSLINE3FIELDNAME = [ADDRESSLINE3FIELD]
      ,@ADDRESSLINE4FIELDNAME = [ADDRESSLINE4FIELD]
      ,@ADDRESSLINE5FIELDNAME = [ADDRESSLINE5FIELD]
      ,@LOOKUPIDFIELDNAME = [LOOKUPIDFIELD]
    from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
    where [ID] = @RECORDSOURCEID;

    -- see if the fields exist in the table/view and clear the name if necessary --

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
      set @FULLNAMEFIELDNAME = '[P].[' + @FULLNAMEFIELDNAME + ']';
    else
      set @FULLNAMEFIELDNAME = '''''';

    set @FULLNAMEFIELDNAME = '(case when [P].[' + @PARENTID + '] is null then ''<Deleted>'' else ' + @FULLNAMEFIELDNAME + ' end)';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTNAMEFIELDNAME)
      set @FIRSTNAMEFIELDNAME = '[P].[' + @FIRSTNAMEFIELDNAME + ']';
    else
      set @FIRSTNAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @MIDDLENAMEFIELDNAME)
      set @MIDDLENAMEFIELDNAME = '[P].[' + @MIDDLENAMEFIELDNAME + ']';
    else
      set @MIDDLENAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LASTNAMEFIELDNAME)
      set @LASTNAMEFIELDNAME = '[P].[' + @LASTNAMEFIELDNAME + ']';
    else
      set @LASTNAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @TITLEFIELDNAME) 
      set @TITLEFIELDNAME = '[P].[' + @TITLEFIELDNAME + ']';
    else
      set @TITLEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @SUFFIXFIELDNAME)
      set @SUFFIXFIELDNAME = '[P].[' + @SUFFIXFIELDNAME + ']';
    else
      set @SUFFIXFIELDNAME = '''''';

    if @USEADDRESSCACHE = 1
      set @COUNTRYFIELDNAME = '[COUNTRY].[DESCRIPTION]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @COUNTRYFIELDNAME)
        set @COUNTRYFIELDNAME = '[P].[' + @COUNTRYFIELDNAME + ']';
      else
        set @COUNTRYFIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @ADDRESSLINE1FIELDNAME = '[ADDRLINES].[ADDRESSLINE1]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE1FIELDNAME)
        set @ADDRESSLINE1FIELDNAME = '[P].[' + @ADDRESSLINE1FIELDNAME + ']';
      else
        set @ADDRESSLINE1FIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @ADDRESSLINE2FIELDNAME = '[ADDRLINES].[ADDRESSLINE2]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE2FIELDNAME)
        set @ADDRESSLINE2FIELDNAME = '[P].[' + @ADDRESSLINE2FIELDNAME + ']';
      else
        set @ADDRESSLINE2FIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @CITYFIELDNAME = '[ADDRESS].[CITY]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CITYFIELDNAME)
        set @CITYFIELDNAME = '[P].[' + @CITYFIELDNAME + ']';
      else
        set @CITYFIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @STATEFIELDNAME = '[STATE].[ABBREVIATION]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @STATEFIELDNAME)
        set @STATEFIELDNAME = '[P].[' + @STATEFIELDNAME + ']';
      else
        if @ISBBEC = 1
        begin
          if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = 'STATEID_TRANSLATION')
            set @STATEFIELDNAME = '[P].[STATEID_TRANSLATION]';
          else
            set @STATEFIELDNAME = '''''';
        end
        else
          set @STATEFIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @POSTCODEFIELDNAME = '[ADDRESS].[POSTCODE]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @POSTCODEFIELDNAME)
        set @POSTCODEFIELDNAME = '[P].[' + @POSTCODEFIELDNAME + ']';
      else
        set @POSTCODEFIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @CARTFIELDNAME = '[ADDRESS].[CART]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CARTFIELDNAME)
        set @CARTFIELDNAME = '[P].[' + @CARTFIELDNAME + ']';
      else
        set @CARTFIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @DPCFIELDNAME = '[ADDRESS].[DPC]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @DPCFIELDNAME)
        set @DPCFIELDNAME = '[P].[' + @DPCFIELDNAME + ']';
      else
        set @DPCFIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @LOTFIELDNAME = '[ADDRESS].[LOT]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOTFIELDNAME)
        set @LOTFIELDNAME = '[P].[' + @LOTFIELDNAME + ']';
      else
        set @LOTFIELDNAME = '''''';
    end

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @PHONENUMBERFIELDNAME)
      set @PHONENUMBERFIELDNAME = '[P].[' + @PHONENUMBERFIELDNAME + ']';
    else
      set @PHONENUMBERFIELDNAME = '''''';

    if @USEEMAILADDRESSCACHE = 1
      set @EMAILADDRESSFIELDNAME = '[EMAILADDRESS].[EMAILADDRESS]';
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @EMAILADDRESSFIELDNAME)
        set @EMAILADDRESSFIELDNAME = '[P].[' + @EMAILADDRESSFIELDNAME + ']';
      else
       set @EMAILADDRESSFIELDNAME = '''''';
    end

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTGIFTDATEFIELDNAME)
      set @FIRSTGIFTDATEFIELDNAME = '[P].[' + @FIRSTGIFTDATEFIELDNAME + ']';
    else
      set @FIRSTGIFTDATEFIELDNAME = '''''';

    if @USEADDRESSCACHE = 1
      set @ADDRESSLINE3FIELDNAME = '[ADDRLINES].[ADDRESSLINE3]';
    else
    begin
      if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE3FIELDNAME)
        set @ADDRESSLINE3FIELDNAME = '[P].[' + @ADDRESSLINE3FIELDNAME + ']';
      else
        set @ADDRESSLINE3FIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @ADDRESSLINE4FIELDNAME = '[ADDRLINES].[ADDRESSLINE4]';
    else
    begin
      if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE4FIELDNAME)
        set @ADDRESSLINE4FIELDNAME = '[P].[' + @ADDRESSLINE4FIELDNAME + ']';
      else
        set @ADDRESSLINE4FIELDNAME = '''''';
    end

    if @USEADDRESSCACHE = 1
      set @ADDRESSLINE5FIELDNAME = '[ADDRLINES].[ADDRESSLINE5]';
    else
    begin
      if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE5FIELDNAME)
        set @ADDRESSLINE5FIELDNAME = '[P].[' + @ADDRESSLINE5FIELDNAME + ']';
      else
        set @ADDRESSLINE5FIELDNAME = '''''';
    end

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOOKUPIDFIELDNAME)
      set @LOOKUPIDFIELDNAME = '[P].[' + @LOOKUPIDFIELDNAME + ']';
    else
      set @LOOKUPIDFIELDNAME = '''''';

    --Build the SQL

    declare @SQL nvarchar(max);

    set @SQL = isnull(@SQL, '') +
               'select top ' + cast(@TOTALROWS as nvarchar(10)) + char(13) +
               '  ' + @FULLNAMEFIELDNAME + ',' + char(13) +
               '  ' + @FIRSTNAMEFIELDNAME + ',' + char(13) +
               '  ' + @MIDDLENAMEFIELDNAME + ',' + char(13) +
               '  ' + @LASTNAMEFIELDNAME + ',' + char(13) +
               '  ' + @TITLEFIELDNAME + ',' + char(13) +
               '  ' + @SUFFIXFIELDNAME + ',' + char(13) +
               '  ' + @COUNTRYFIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE1FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE2FIELDNAME + ',' + char(13) +
               '  ' + @CITYFIELDNAME + ',' + char(13) +
               '  ' + @STATEFIELDNAME + ',' + char(13) +
               '  ' + @POSTCODEFIELDNAME + ',' + char(13) +
               '  ' + @CARTFIELDNAME + ',' + char(13) +
               '  ' + @DPCFIELDNAME + ',' + char(13) +
               '  ' + @LOTFIELDNAME + ',' + char(13) +
               '  ' + @PHONENUMBERFIELDNAME + ',' + char(13) +
               '  ' + @EMAILADDRESSFIELDNAME + ',' + char(13) +
               '  ' + @FIRSTGIFTDATEFIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE3FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE4FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE5FIELDNAME + ',' + char(13) +
               '  ' + @LOOKUPIDFIELDNAME + ',' + char(13) +
               '  [P].[' + @PARENTID + '],' + char(13) +
               '  (case when [DONORS].[FINDERNUMBER] < 0 then null else right(N''00000000000000000000'' + cast([DONORS].[FINDERNUMBER] as nvarchar(20)), ' + @FIXEDWIDTH + ') end) as [FINDERNUMBER],' + char(13) +
               (case when @RECORDSOURCETYPE = 1 then
                  '  1'
                when @RECORDSOURCETYPE = 2 then
                  '  0'
                else
                  '  (case when ''' + cast(@RECORDSOURCEID as nvarchar(36)) + ''' = [DONORS].[DONORQUERYVIEWCATALOGID] then 1 else 0 end)'
                end) + ' as [ENABLEGOTO]' + char(13) +
               'from dbo.[' + @SEGMENTDONORSTABLENAME + '] as [DONORS]' + char(13);

    --For BBEC only, check to see if we have any merged constituents in this mailing.  If so, then we want to show the original constituent.

    if @ISBBEC = 1 and exists(select * from dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] where [SEGMENTATIONID] = @SEGMENTATIONID)
      begin
        set @SQL += 'left join dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] on [MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS].[FINDERNUMBER] = [DONORS].[FINDERNUMBER]' + char(13);
        set @DONORIDSQL = 'isnull([MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS].[ORIGINALCONSTITUENTID], [DONORS].[DONORID])';
      end
    else
      set @DONORIDSQL = '[DONORS].[DONORID]';

    set @SQL += 'left join dbo.[' + @PARENTTABLENAME + '] as [P] on [P].[' + @PARENTID + '] = ' + @DONORIDSQL + char(13);

    if @USEADDRESSCACHE = 1
      set @SQL += 'left join dbo.[' + @ADDRESSCACHETABLENAME + '] as [ADDRESSCACHE] on [ADDRESSCACHE].[SEGMENTID] = @MAILINGSEGMENTID and [ADDRESSCACHE].[CONSTITUENTID] = [P].[' + @PARENTID + ']' + char(13) +
                  'left join dbo.[ADDRESS] on [ADDRESS].[ID] = [ADDRESSCACHE].[ADDRESSID]'+ char(13) +
                  'left join dbo.[COUNTRY] on [COUNTRY].[ID] = [ADDRESS].[COUNTRYID]'+ char(13) +
                  'left join dbo.[STATE] on [STATE].[ID] = [ADDRESS].[STATEID]'+ char(13) +
                  'outer apply dbo.[UFN_ADDRESS_GETADDRESSLINES]([ADDRESS].[ADDRESSBLOCK]) as [ADDRLINES]' + char(13);

    if @USEEMAILADDRESSCACHE = 1
      set @SQL += 'left join dbo.[' + @EMAILADDRESSCACHETABLENAME + '] as [EMAILADDRESS] on [EMAILADDRESS].[SEGMENTID] = @MAILINGSEGMENTID and [EMAILADDRESS].[CONSTITUENTID] = [P].[' + @PARENTID + ']' + char(13);

    if @ISTESTSEGMENT = 0
      set @SQL += 'where [DONORS].[SEGMENTID] = @MAILINGSEGMENTID' + char(13) +
                  'and [DONORS].[TESTSEGMENTID] is null' + char(13);
    else
      set @SQL += 'where [DONORS].[TESTSEGMENTID] = @MAILINGSEGMENTID' + char(13);

    --Filter the records, if specified

    declare @TEMPFILTER nvarchar(255);
    if @FILTER is not null and len(@FILTER) > 0
      begin
        set @TEMPFILTER = '%' + @FILTER + '%';
        set @SQL += 'and (' + @FULLNAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @FIRSTNAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MIDDLENAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @LASTNAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @TITLEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @SUFFIXFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @COUNTRYFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE1FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE2FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @CITYFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @STATEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @POSTCODEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @CARTFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @DPCFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @LOTFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @PHONENUMBERFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @EMAILADDRESSFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @FIRSTGIFTDATEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE3FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE4FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE5FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @LOOKUPIDFIELDNAME + ' like @FILTER' + char(13) +
                   '  or (case when [DONORS].[FINDERNUMBER] < 0 then null else right(N''00000000000000000000'' + cast([DONORS].[FINDERNUMBER] as nvarchar(20)), ' + @FIXEDWIDTH + ') end) like @FILTER)' + char(13);
      end

    --Do NOT order the results here, for large amounts of data it takes too long for not much benefit.


    exec sp_executesql @SQL, N'@MAILINGSEGMENTID uniqueidentifier, @FILTER nvarchar(255)', @MAILINGSEGMENTID = @MAILINGSEGMENTID, @FILTER = @TEMPFILTER;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;