USP_MKTSEGMENTLIST_FINDANDUPDATEDUPLICATES

CREATE procedure dbo.[USP_MKTSEGMENTLIST_FINDANDUPDATEDUPLICATES]
(
  @SEGMENTID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTLISTID uniqueidentifier;
  declare @MINIMUMDATAID uniqueidentifier;
  declare @MAXIMUMDATAID uniqueidentifier;
  declare @LISTQUERYVIEWCATALOGID uniqueidentifier;
  declare @DUPLICATEID uniqueidentifier;
  declare @CONSOLIDATEDQUERYVIEWCATALOGID uniqueidentifier;
  declare @QUERYVIEWNAME nvarchar(128);

  declare @FIELDNAME nvarchar(100);
  declare @FIELDLENGTH smallint;
  declare @TOTALFIELDLENGTH int = 0;
  declare @SQL nvarchar(max) = '';
  declare @DUPLICATEFIELDSSQL nvarchar(max) = '';

  declare @RECORDSOURCEID uniqueidentifier;
  declare @ISENCRYPTED bit;
  declare @GRANTKEYACCESS bit = 0;

  begin try
    select
      @SEGMENTLISTID = [MKTSEGMENTLIST].[ID],
      @MINIMUMDATAID = [MKTSEGMENTLIST].[MINIMUMDATAID],
      @MAXIMUMDATAID = [MKTSEGMENTLIST].[MAXIMUMDATAID],
      @LISTQUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
      @DUPLICATEID = [MKTDUPLICATE].[ID],
      @CONSOLIDATEDQUERYVIEWCATALOGID = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID],
      @QUERYVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
      @RECORDSOURCEID = [MKTLIST].[RECORDSOURCEID]
    from dbo.[MKTSEGMENT]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    left join dbo.[MKTDUPLICATE] on [MKTDUPLICATE].[QUERYVIEWCATALOGID] = [MKTLIST].[RECORDSOURCEID]
    left join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [MKTLIST].[RECORDSOURCEID]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [MKTLIST].[RECORDSOURCEID])
    where [MKTSEGMENT].[ID] = @SEGMENTID;

    --Only dedupe if duplicate criteria has been defined for this record source...

    if @DUPLICATEID is not null and exists(select top 1 1 from dbo.[MKTDUPLICATEFIELD] where [DUPLICATEID] = @DUPLICATEID) and dbo.[UFN_MKTSEGMENTLIST_DUPLICATEFIELDSEXIST](@SEGMENTLISTID) = 1
      begin
        --Reset all the duplicates for this list segment (NOTE: it is quicker to use the non-clustered index field SEGMENTLISTID in the 

        --where clause in this case because the index also includes the ISDUPLICATE field.  Do not change to use MIN and MAX IDs).

        update dbo.[MKTSEGMENTLISTDATA] set
          [ISDUPLICATE] = 0
        where [SEGMENTLISTID] = @SEGMENTLISTID
        and [ISDUPLICATE] = 1;

        --Create field SQL to use to determine duplicate records...

        declare DUPLICATECURSOR cursor local fast_forward for
          select
            [FIELDNAME],
            [FIELDLENGTH],
            dbo.[UFN_MKTSEGMENTLIST_ISFIELDENCRYPTED](@RECORDSOURCEID, [FIELDNAME])
          from dbo.[MKTDUPLICATEFIELD]
          where [DUPLICATEID] = @DUPLICATEID;

        open DUPLICATECURSOR;
        fetch next from DUPLICATECURSOR into @FIELDNAME, @FIELDLENGTH, @ISENCRYPTED;

        while (@@FETCH_STATUS = 0)
        begin
          if len(@DUPLICATEFIELDSSQL) > 0
            set @DUPLICATEFIELDSSQL = @DUPLICATEFIELDSSQL + ' + ';

          --Need to use ltrim() and rtrim() here to remove any leading/trailing spaces from the field values...

          if @ISENCRYPTED = 1
            set @GRANTKEYACCESS = 1;

          set @DUPLICATEFIELDSSQL = @DUPLICATEFIELDSSQL + 'isnull(left(ltrim(rtrim([L].[' + @FIELDNAME + '])), ' + cast(@FIELDLENGTH as nvarchar) + '), '''')';
          set @TOTALFIELDLENGTH += @FIELDLENGTH;

          fetch next from DUPLICATECURSOR into @FIELDNAME, @FIELDLENGTH, @ISENCRYPTED;
        end

        close DUPLICATECURSOR;
        deallocate DUPLICATECURSOR;

        --Find and mark all the duplicate records...

        if @GRANTKEYACCESS = 1
          set @SQL = 'exec dbo.[USP_GET_KEY_ACCESS];' + char(13) + char(13);

        --To improve matching performance of the update statement below, store the dupkey for all records in the housefile in a temp table first...

        set @SQL += 'create table #HOUSEFILE (' + char(13) +
                    '  [DUPKEY] nvarchar(' + cast(@TOTALFIELDLENGTH as nvarchar(10)) + ') collate database_default not null' + char(13) +
                    ');' + char(13) +
                    char(13) +
                    'insert into #HOUSEFILE ([DUPKEY])' + char(13) +
                    '  select (' + replace(@DUPLICATEFIELDSSQL, '[L].', '') + ')' + char(13) +
                    '  from dbo.[' + @QUERYVIEWNAME + ']';

        --If a consolidated query exists for this record source, then exclude the current list segment we are processing...

        if @CONSOLIDATEDQUERYVIEWCATALOGID is not null
          set @SQL += char(13) + '  where [SOURCEQUERYVIEWID] <> @LISTQUERYVIEWCATALOGID';

        set @SQL += ';' + char(13) +
                    char(13) +
                    'create nonclustered index [IX_HOUSEFILE_DUPKEY] on #HOUSEFILE ([DUPKEY]);' + char(13) +
                    char(13) +
                    'update dbo.[MKTSEGMENTLISTDATA]' + char(13) +
                    '  set [ISDUPLICATE] = 1' + char(13);

        if @MINIMUMDATAID is not null and @MAXIMUMDATAID is not null
          --Use the clustered index of sequential IDs...

          set @SQL += 'from dbo.[MKTSEGMENTLISTDATA] as [L]' + char(13) +
                      'left join #HOUSEFILE as [HOUSEFILE] on [HOUSEFILE].[DUPKEY] = (' + @DUPLICATEFIELDSSQL + ')' + char(13) +
                      'where [L].[ID] between @MINIMUMDATAID and @MAXIMUMDATAID' + char(13);
        else
          --Force an index seek operation by specifying an index hint, otherwise it will do a slower index scan...

          set @SQL += 'from dbo.[MKTSEGMENTLISTDATA] as [L] with (INDEX([IX_MKTSEGMENTLISTDATA_SEGMENTLISTID]))' + char(13) +
                      'left join #HOUSEFILE as [HOUSEFILE] on [HOUSEFILE].[DUPKEY] = (' + @DUPLICATEFIELDSSQL + ')' + char(13) +
                      'where [L].[SEGMENTLISTID] = @SEGMENTLISTID' + char(13);

        --Check if the dupkeys match or if the list member has given a gift in the past and been matched back by another mailing...

        set @SQL += 'and ([HOUSEFILE].[DUPKEY] is not null or exists(select * from dbo.[' + dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME](@RECORDSOURCEID) + '] where [ID] = [L].[ID])';

        if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
          --If it is a BBEC record source, then also check if the list member ID is equal to a constituent ID...

          set @SQL += ' or exists(select * from dbo.[CONSTITUENT] where [ID] = [L].[ID])';

        set @SQL += ');' + char(13) +
                    char(13) +
                    'drop table #HOUSEFILE;';

        if @GRANTKEYACCESS = 1
          set @SQL += char(13) + char(13) + 'close symmetric key sym_BBInfinity;';

        if @MINIMUMDATAID is not null and @MAXIMUMDATAID is not null
          exec sp_executesql @SQL, N'@MINIMUMDATAID uniqueidentifier, @MAXIMUMDATAID uniqueidentifier, @LISTQUERYVIEWCATALOGID uniqueidentifier', @MINIMUMDATAID = @MINIMUMDATAID, @MAXIMUMDATAID = @MAXIMUMDATAID, @LISTQUERYVIEWCATALOGID = @LISTQUERYVIEWCATALOGID;
        else
          exec sp_executesql @SQL, N'@SEGMENTLISTID uniqueidentifier, @LISTQUERYVIEWCATALOGID uniqueidentifier', @SEGMENTLISTID = @SEGMENTLISTID, @LISTQUERYVIEWCATALOGID = @LISTQUERYVIEWCATALOGID;
      end
  end try

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

  return 0;