USP_MKTSEGMENTATION_MATCHBACKDELETE

CREATE procedure dbo.[USP_MKTSEGMENTATION_MATCHBACKDELETE]
(
  @SEGMENTATIONID uniqueidentifier,
  @FORMATCHBACK bit = 0
)
with execute as owner
as
  set nocount on;

  declare @DATATABLENAME nvarchar(128);
  declare @LISTMATCHBACKTABLE nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    if @FORMATCHBACK is null
      set @FORMATCHBACK = 0;

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

    declare RECORDSOURCECURSOR cursor local fast_forward for
      select dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([QUERYVIEWCATALOGID])
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID);

    open RECORDSOURCECURSOR;
    fetch next from RECORDSOURCECURSOR into @LISTMATCHBACKTABLE;

    while (@@FETCH_STATUS = 0)
    begin
      --Check if the activated table exists before we try deleting any vendor managed list matchback donors...

      if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLENAME)
        begin
          --Delete from the mailing activated data table...

          set @SQL = 'delete from dbo.[' + @DATATABLENAME + ']' + char(13) +
                     'where [DONORQUERYVIEWCATALOGID] is null';
          exec sp_executesql @SQL;
        end

      --Only delete from the list matchback table, because we don't want to be deleting the records they imported.

      if @FORMATCHBACK = 1
        begin
          --Only delete vendor managed list records, because the matchback process will automatically clean up the import list records,

          --if necessary, by using a "merge" statement for better performance.  That way we don't have to delete all the records and 

          --reinsert them.  The merge will only update, insert, or delete the rows as necessary.  We cannot use the "merge" statement

          --multiple times to delete records like we need to for vendor managed list matchback, so only delete vendor managed list records here.

          set @SQL = 'delete from dbo.[' + @LISTMATCHBACKTABLE + ']' + char(13) +
                     'from dbo.[' + @LISTMATCHBACKTABLE + '] as [MATCHBACK]' + char(13) +
                     'left join dbo.[MKTSEGMENTLISTDATA] on [MKTSEGMENTLISTDATA].[ID] = [MATCHBACK].[ID]' + char(13) +
                     'where [MATCHBACK].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
                     'and [MKTSEGMENTLISTDATA].[ID] is null';
          exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
        end
      else
        begin
          set @SQL = 'delete from dbo.[' + @LISTMATCHBACKTABLE + ']' + char(13) +
                     'where [SEGMENTATIONID] = @SEGMENTATIONID';
          exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
        end

      fetch next from RECORDSOURCECURSOR into @LISTMATCHBACKTABLE;
    end

    close RECORDSOURCECURSOR;
    deallocate RECORDSOURCECURSOR;
  end try

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

  return 0;