USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_ROLLBACK

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_ROLLBACK]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @TRUNCATEDATATABLE bit = 0
)
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @DATATABLE nvarchar(128);
  declare @FINDERTABLE nvarchar(128);
  declare @CURRENTDATE datetime;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTTEMP nvarchar(128);
  declare @SEGMENTTEMPNTH nvarchar(128);
  declare @SEGMENTATIONFINDERNUMBERID uniqueidentifier;

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
    set @FINDERTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKEFINDERTABLENAME](@SEGMENTATIONID);

    -- delete all the segment temporary tables that may still exist


    declare SEGMENTCURSOR cursor local fast_forward for
      select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID;

    while (@@FETCH_STATUS = 0)
    begin
      set @SEGMENTTEMP = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETEMPSEGMENTTABLENAME](@SEGMENTID);
      if exists(select top 1 1 from tempdb.[INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @SEGMENTTEMP)
        begin
          set @SQL = 'drop table dbo.[' + @SEGMENTTEMP + ']';
          exec (@SQL);
        end

      set @SEGMENTTEMPNTH = '##TEMP_NTH_' + replace(cast(@SEGMENTID as nvarchar(36)), '-', '_');
      if exists(select top 1 1 from tempdb.[INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @SEGMENTTEMPNTH)
        begin
          set @SQL = 'drop table dbo.[' + @SEGMENTTEMPNTH + ']';
          exec (@SQL);
        end

      exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTID, 0, 0;

      fetch next from SEGMENTCURSOR into @SEGMENTID;
    end

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

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

        if @TRUNCATEDATATABLE = 1
          exec dbo.[USP_MKTSEGMENTATION_TRUNCATEDATATABLE] @SEGMENTATIONID;
        else
          begin
            --Remove any rows with a null finder number because they haven't been fully calculated anyway...

            set @SQL = 'delete from dbo.[' + @DATATABLE + '] where [FINDERNUMBER] is null';
            exec (@SQL);
          end
      end

      set @SQL = 'if object_id(''tempdb..##' + @FINDERTABLE + ''') is not null' + char(13) +
                  '  drop table dbo.[##' + @FINDERTABLE + '];';
      exec (@SQL);

    exec dbo.[USP_MKTSEGMENTATION_CLEARCACHE] @SEGMENTATIONID, 0;
  end try

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

  return 0;