USP_DATALIST_MKTSEGMENTMEMBERSHIP_MEMBER

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

  declare @PARENTTABLENAME nvarchar(255); 
  declare @PARENTID nvarchar(255); 
  declare @SEGMENTVIEWNAME nvarchar(255); 
  declare @RECORDSOURCEID uniqueidentifier;

  select
    @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
    @PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
    @PARENTID = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
    @SEGMENTVIEWNAME = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([MKTSEGMENT].[ID])
  from dbo.[MKTSEGMENT]
  inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  where [MKTSEGMENT].[ID] = @SEGMENTID;

  -- 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 @LOOKUPIDFIELDNAME nvarchar(255);

  declare @MEMBERSHIPVIEWNAME nvarchar(128);
  declare @MEMBERSHIPVIEWPRIMARYKEYFIELD nvarchar(255)
  declare @MEMBERSHIPVIEWMEMBERIDFIELD 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]
      ,@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]
      ,@MEMBERSHIPVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME]
      ,@MEMBERSHIPVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD]
      ,@MEMBERSHIPVIEWMEMBERIDFIELD = [MKTMEMBERSHIPRECORDSOURCE].[MEMBERIDFIELD]
      ,@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]
  inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
  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] = @PARENTTABLENAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
    set @FULLNAMEFIELDNAME = '[P].[' + @FULLNAMEFIELDNAME + ']';
  else
    set @FULLNAMEFIELDNAME = '''''';

  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 exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @COUNTRYFIELDNAME)
    set @COUNTRYFIELDNAME = '[P].[' + @COUNTRYFIELDNAME + ']';
  else
    set @COUNTRYFIELDNAME = '''''';

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

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

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

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

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

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

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

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

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

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

  if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTGIFTDATEFIELDNAME)
    set @FIRSTGIFTDATEFIELDNAME = '[P].[' + @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 = '''''';

  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] = @PARENTTABLENAME and [COLUMN_NAME] = @LOOKUPIDFIELDNAME)
      set @LOOKUPIDFIELDNAME = '[P].[' + @LOOKUPIDFIELDNAME + ']';
    else
      set @LOOKUPIDFIELDNAME = '''''';

  --Build the SQL

  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) +
             '  ' + @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) +
             '  [P].[' + @PARENTID + '],' + char(13) +
             '  [M].[' + @MEMBERSHIPVIEWPRIMARYKEYFIELD + '],' + char(13) +
             '  ' + @LOOKUPIDFIELDNAME + ',' + char(13) +
             '  ' + @MEMBERSHIPLOOKUPIDFIELDNAME + char(13) +
             'from dbo.[' + @SEGMENTVIEWNAME + '] as [V]' + char(13) +
             'inner join dbo.[' + @MEMBERSHIPVIEWNAME + '] as [M] on [M].[' + @MEMBERSHIPVIEWPRIMARYKEYFIELD + '] = [V].[ID] and [M].[ISPRIMARY] = 1 and [M].[ISDROPPED] = 0' + char(13) +
             'inner join dbo.[' + @PARENTTABLENAME + '] as [P] on [P].[' + @PARENTID + '] = [M].[' + @MEMBERSHIPVIEWMEMBERIDFIELD + ']' + 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 = @SQL + 'where (' + @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);
    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'@FILTER nvarchar(255)', @FILTER = @TEMPFILTER;

  return 0;