USP_DATALIST_MKTSEGMENTATIONSEGMENT_MEMBERMEMBERSHIP

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

  declare @SEGMENTATIONID uniqueidentifier;
  declare @ISTESTSEGMENT bit;
  declare @DONORVIEWNAME nvarchar(128); 
  declare @DONORIDFIELDNAME nvarchar(255); 
  declare @MEMBERSHIPVIEWNAME nvarchar(128);
  declare @MEMBERSHIPIDFIELDNAME nvarchar(255);
  declare @SEGMENTMEMBERSHIPSTABLENAME nvarchar(255); 
  declare @RECORDSOURCEID uniqueidentifier;
  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,
        @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
        @MEMBERSHIPVIEWNAME = [MQVC].[OBJECTNAME],
        @MEMBERSHIPIDFIELDNAME = [MQVC].[PRIMARYKEYFIELD],
        @DONORVIEWNAME = [DQVC].[OBJECTNAME],
        @DONORIDFIELDNAME = [DQVC].[PRIMARYKEYFIELD],
        @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]
      inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      inner join dbo.[QUERYVIEWCATALOG] as [DQVC] on [DQVC].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      inner join dbo.[QUERYVIEWCATALOG] as [MQVC] on [MQVC].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @MAILINGSEGMENTID;
    else
      select
        @ISTESTSEGMENT = 1,
        @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
        @MEMBERSHIPVIEWNAME = [MQVC].[OBJECTNAME],
        @MEMBERSHIPIDFIELDNAME = [MQVC].[PRIMARYKEYFIELD],
        @DONORVIEWNAME = [DQVC].[OBJECTNAME],
        @DONORIDFIELDNAME = [DQVC].[PRIMARYKEYFIELD],
        @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]
      inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      inner join dbo.[QUERYVIEWCATALOG] as [DQVC] on [DQVC].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      inner join dbo.[QUERYVIEWCATALOG] as [MQVC] on [MQVC].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = (select [SEGMENTID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @MAILINGSEGMENTID);

    set @SEGMENTMEMBERSHIPSTABLENAME = 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 = @SEGMENTMEMBERSHIPSTABLENAME))
      exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CREATEDATATABLE] @SEGMENTATIONID;

    -- 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);

    declare @MEMBERSHIPPROGRAMFIELDNAME nvarchar(255);
    declare @MEMBERSHIPLEVELFIELDNAME nvarchar(255);
    declare @MEMBERSHIPTERMFIELDNAME nvarchar(255);
    declare @MEMBERSHIPSTATUSFIELDNAME nvarchar(255);
    declare @MEMBERSHIPEXPIRATIONDATEFIELDNAME nvarchar(255);
    declare @MEMBERSHIPLOOKUPIDFIELDNAME nvarchar(255);

    -- grab the field names --

    select
      @FULLNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FULLNAMEFIELD],
      @FIRSTNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FIRSTNAMEFIELD],
      @MIDDLENAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[MIDDLENAMEFIELD],
      @LASTNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LASTNAMEFIELD],
      @TITLEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[TITLEFIELD],
      @SUFFIXFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[SUFFIXFIELD],
      @COUNTRYFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[COUNTRYFIELD],
      @ADDRESSLINE1FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE1FIELD],
      @ADDRESSLINE2FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE2FIELD],
      @ADDRESSLINE3FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE3FIELD],
      @ADDRESSLINE4FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE4FIELD],
      @ADDRESSLINE5FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE5FIELD],
      @CITYFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[CITYFIELD],
      @STATEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[STATEFIELD],
      @POSTCODEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[POSTCODEFIELD],
      @CARTFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[CARTFIELD],
      @DPCFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[DPCFIELD],
      @LOTFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LOTFIELD],
      @PHONENUMBERFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[PHONENUMBERFIELD],
      @EMAILADDRESSFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[EMAILADDRESSFIELD],
      @FIRSTGIFTDATEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FIRSTGIFTDATEFIELD],
      @MEMBERSHIPPROGRAMFIELDNAME = [MKTMEMBERSHIPRECORDSOURCE].[PROGRAMFIELD],
      @MEMBERSHIPLEVELFIELDNAME = [MKTMEMBERSHIPRECORDSOURCE].[LEVELFIELD],
      @MEMBERSHIPTERMFIELDNAME = [MKTMEMBERSHIPRECORDSOURCE].[TERMFIELD],
      @MEMBERSHIPSTATUSFIELDNAME = [MKTMEMBERSHIPRECORDSOURCE].[STATUSFIELD],
      @MEMBERSHIPEXPIRATIONDATEFIELDNAME = [MKTMEMBERSHIPRECORDSOURCE].[EXPIRATIONDATEFIELD],
      @MEMBERSHIPLOOKUPIDFIELDNAME = [MKTMEMBERSHIPRECORDSOURCE].[LOOKUPIDFIELD],
      @LOOKUPIDFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LOOKUPIDFIELD]
    from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
    inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[ID] = [MKTRECORDSOURCEFIELDMAPPINGS].[ID]
    where [MKTRECORDSOURCEFIELDMAPPINGS].[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] = @DONORVIEWNAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
      set @FULLNAMEFIELDNAME = '[D].[' + @FULLNAMEFIELDNAME + ']';
    else
      set @FULLNAMEFIELDNAME = '''''';

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

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

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

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

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

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

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @COUNTRYFIELDNAME)
      set @COUNTRYFIELDNAME = '[D].[' + @COUNTRYFIELDNAME + ']';
    else
      set @COUNTRYFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE1FIELDNAME)
      set @ADDRESSLINE1FIELDNAME = '[D].[' + @ADDRESSLINE1FIELDNAME + ']';
    else
      set @ADDRESSLINE1FIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE2FIELDNAME)
      set @ADDRESSLINE2FIELDNAME = '[D].[' + @ADDRESSLINE2FIELDNAME + ']';
    else
      set @ADDRESSLINE2FIELDNAME = '''''';

    if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE3FIELDNAME)
      set @ADDRESSLINE3FIELDNAME = '[D].[' + @ADDRESSLINE3FIELDNAME + ']';
    else
      set @ADDRESSLINE3FIELDNAME = '''''';

    if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE4FIELDNAME)
      set @ADDRESSLINE4FIELDNAME = '[D].[' + @ADDRESSLINE4FIELDNAME + ']';
    else
      set @ADDRESSLINE4FIELDNAME = '''''';

    if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE5FIELDNAME)
      set @ADDRESSLINE5FIELDNAME = '[D].[' + @ADDRESSLINE5FIELDNAME + ']';
    else
      set @ADDRESSLINE5FIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @CITYFIELDNAME)
      set @CITYFIELDNAME = '[D].[' + @CITYFIELDNAME + ']';
    else
      set @CITYFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @STATEFIELDNAME)
      set @STATEFIELDNAME = '[D].[' + @STATEFIELDNAME + ']';
    else
      set @STATEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @POSTCODEFIELDNAME)
      set @POSTCODEFIELDNAME = '[D].[' + @POSTCODEFIELDNAME + ']';
    else
      set @POSTCODEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @CARTFIELDNAME)
      set @CARTFIELDNAME = '[D].[' + @CARTFIELDNAME + ']';
    else
      set @CARTFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @DPCFIELDNAME)
      set @DPCFIELDNAME = '[D].[' + @DPCFIELDNAME + ']';
    else
      set @DPCFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @LOTFIELDNAME)
      set @LOTFIELDNAME = '[D].[' + @LOTFIELDNAME + ']';
    else
      set @LOTFIELDNAME = '''''';

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

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @DONORVIEWNAME and [COLUMN_NAME] = @EMAILADDRESSFIELDNAME)
      set @EMAILADDRESSFIELDNAME = '[D].[' + @EMAILADDRESSFIELDNAME + ']';
    else
      set @EMAILADDRESSFIELDNAME = '''''';

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

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @MEMBERSHIPVIEWNAME and [COLUMN_NAME] = @MEMBERSHIPPROGRAMFIELDNAME)
      set @MEMBERSHIPPROGRAMFIELDNAME = '[M].[' + @MEMBERSHIPPROGRAMFIELDNAME + ']';
    else
      set @MEMBERSHIPPROGRAMFIELDNAME = '''''';

    set @MEMBERSHIPPROGRAMFIELDNAME = '(case when [M].[' + @MEMBERSHIPIDFIELDNAME + '] is null then ''<Deleted>'' else ' + @MEMBERSHIPPROGRAMFIELDNAME + ' end)';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @MEMBERSHIPVIEWNAME and [COLUMN_NAME] = @MEMBERSHIPLEVELFIELDNAME)
      set @MEMBERSHIPLEVELFIELDNAME = '[M].[' + @MEMBERSHIPLEVELFIELDNAME + ']';
    else
      set @MEMBERSHIPLEVELFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @MEMBERSHIPVIEWNAME and [COLUMN_NAME] = @MEMBERSHIPTERMFIELDNAME)
      set @MEMBERSHIPTERMFIELDNAME = '[M].[' + @MEMBERSHIPTERMFIELDNAME + ']';
    else
      set @MEMBERSHIPTERMFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @MEMBERSHIPVIEWNAME and [COLUMN_NAME] = @MEMBERSHIPSTATUSFIELDNAME)
      set @MEMBERSHIPSTATUSFIELDNAME = '[M].[' + @MEMBERSHIPSTATUSFIELDNAME + ']';
    else
      set @MEMBERSHIPSTATUSFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @MEMBERSHIPVIEWNAME and [COLUMN_NAME] = @MEMBERSHIPEXPIRATIONDATEFIELDNAME)
      set @MEMBERSHIPEXPIRATIONDATEFIELDNAME = '[M].[' + @MEMBERSHIPEXPIRATIONDATEFIELDNAME + ']';
    else
      set @MEMBERSHIPEXPIRATIONDATEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @MEMBERSHIPVIEWNAME and [COLUMN_NAME] = @MEMBERSHIPLOOKUPIDFIELDNAME)
      set @MEMBERSHIPLOOKUPIDFIELDNAME = '[M].[' + @MEMBERSHIPLOOKUPIDFIELDNAME + ']';
    else
      set @MEMBERSHIPLOOKUPIDFIELDNAME = '''''';

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

    declare @SQL nvarchar(max);

    set @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) +
               '  ' + @MEMBERSHIPPROGRAMFIELDNAME + ',' + char(13) +
               '  ' + @MEMBERSHIPLEVELFIELDNAME + ',' + char(13) +
     '  ' + @MEMBERSHIPTERMFIELDNAME + ',' + char(13) +
               '  ' + @MEMBERSHIPSTATUSFIELDNAME + ',' + char(13) +
               '  ' + @MEMBERSHIPEXPIRATIONDATEFIELDNAME + ',' + char(13) +
               '  ' + @COUNTRYFIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE1FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE2FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE3FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE4FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE5FIELDNAME + ',' + 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) +
               '  [D].[' + @DONORIDFIELDNAME + '],' + char(13) +
               '  [M].[' + @MEMBERSHIPIDFIELDNAME + '],' + char(13) +
               '  ' + @LOOKUPIDFIELDNAME + ',' + char(13) +
               '  ' + @MEMBERSHIPLOOKUPIDFIELDNAME + ',' + char(13) +
               '  (case when [MEMBERSHIPS].[FINDERNUMBER] < 0 then null else right(N''00000000000000000000'' + cast([MEMBERSHIPS].[FINDERNUMBER] as nvarchar(20)), ' + @FIXEDWIDTH + ') end) as [FINDERNUMBER]' + char(13) +
               'from dbo.[' + @SEGMENTMEMBERSHIPSTABLENAME + '] as [MEMBERSHIPS]' + char(13) +
               'left join dbo.[' + @MEMBERSHIPVIEWNAME + '] as [M] on [M].[' + @MEMBERSHIPIDFIELDNAME + '] = [MEMBERSHIPS].[MEMBERSHIPID] and [M].[ISPRIMARY] = 1 and [M].[ISDROPPED] = 0' + 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] = [MEMBERSHIPS].[FINDERNUMBER]' + char(13);
        set @DONORIDSQL = 'isnull([MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS].[ORIGINALCONSTITUENTID], [MEMBERSHIPS].[DONORID])';
      end
    else
      set @DONORIDSQL = '[MEMBERSHIPS].[DONORID]';

    set @SQL += 'left join dbo.[' + @DONORVIEWNAME + '] as [D] on [D].[' + @DONORIDFIELDNAME + '] = ' + @DONORIDSQL + char(13);

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

    -- filter the records if a filter is 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 ' + @ADDRESSLINE3FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE4FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE5FIELDNAME + ' 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 ' + @LOOKUPIDFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MEMBERSHIPLOOKUPIDFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MEMBERSHIPPROGRAMFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MEMBERSHIPLEVELFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MEMBERSHIPTERMFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MEMBERSHIPSTATUSFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MEMBERSHIPEXPIRATIONDATEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or (case when [MEMBERSHIPS].[FINDERNUMBER] < 0 then '''' else right(N''00000000000000000000'' + cast([MEMBERSHIPS].[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;