USP_MKTSEGMENTATION_TRUNCATEDATATABLE

CREATE procedure dbo.[USP_MKTSEGMENTATION_TRUNCATEDATATABLE]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @DATATABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
  declare @SQL nvarchar(max);
  declare @TRUNCATESUCCEEDED bit;
  declare @RETRY tinyint;
  declare @OLDDONORIDDATATYPE nvarchar(128);
  declare @NEWDONORIDDATATYPE nvarchar(128);

  select
    @MAILINGTYPECODE = [MAILINGTYPECODE]
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;

  --For direct/appeal mailings only, check to see if the data type of the DONORID column changed (ie - can happen by adding a list segment to the mailing after adding RE7 house file segments)...

  if @MAILINGTYPECODE = 0
    begin
      select
        @OLDDONORIDDATATYPE = (case when lower([DATA_TYPE]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast([CHARACTER_MAXIMUM_LENGTH] as nvarchar(10)) + ')' else [DATA_TYPE] end)
      from [INFORMATION_SCHEMA].[COLUMNS]
      where [TABLE_SCHEMA] = 'dbo'
      and [TABLE_NAME] = @DATATABLENAME
      and [COLUMN_NAME] = 'DONORID';

      set @NEWDONORIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID);
    end

  if isnull(@OLDDONORIDDATATYPE, '') <> isnull(@NEWDONORIDDATATYPE, '')
    begin
      --The DONORID data type changed so we need to drop the table (if exists) and recreate it...

      if exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLENAME)
        begin
          set @SQL = 'drop table dbo.[' + @DATATABLENAME + ']';
          exec (@SQL);
        end

      exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CREATEDATATABLE] @SEGMENTATIONID;
    end
  else
    begin
    if exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLENAME)
      begin
        -- try to recover from deadlocks when truncating the activated table by catching any deadlocks and keep retrying until

        -- the table is truncated successfully

        -- this will retry every 10 seconds for 180 times before it raises an error


        set @TRUNCATESUCCEEDED = 0;
        set @RETRY = 0;
        set @SQL = 'truncate table dbo.[' + @DATATABLENAME + '];';

        while (@TRUNCATESUCCEEDED = 0)
        begin
        begin try
          exec (@SQL);
          set @TRUNCATESUCCEEDED = 1;
        end try
        begin catch
          if ERROR_NUMBER() = 1205 and @RETRY < 180
          begin
            waitfor delay '00:00:10';
            set @RETRY = @RETRY + 1;
          end
          else
          begin
            exec dbo.USP_RAISE_ERROR;
            return 1;
          end
        end catch
        end


        -- drop indexes created in USP_MKTSEGMENTATIONACTIVATE_ACTIVATE

        set @SQL = 'if exists(select * from dbo.sysindexes where name = ''IX_' + @DATATABLENAME + '_SEGMENTID'')' + char(13) +
                   '  drop index [IX_' + @DATATABLENAME + '_SEGMENTID] on [dbo].[' + @DATATABLENAME + '];' + char(13) +
                   char(13) +
                   'if exists(select * from dbo.sysindexes where name = ''IX_' + @DATATABLENAME + '_TESTSEGMENTID'')' + char(13) +
                   '  drop index [IX_' + @DATATABLENAME + '_TESTSEGMENTID] on [dbo].[' + @DATATABLENAME + '];' + char(13) +
                   char(13) +
                   'if exists(select * from dbo.sysindexes where name = ''IX_' + @DATATABLENAME + '_SOURCECODE'')' + char(13) +
                   '  drop index [IX_' + @DATATABLENAME + '_SOURCECODE] on [dbo].[' + @DATATABLENAME + '];' + char(13) +
                   char(13) +
                   'if exists(select * from dbo.sysindexes where name = ''IX_' + @DATATABLENAME + '_ASKLADDERID'')' + char(13) +
                   '  drop index [IX_' + @DATATABLENAME + '_ASKLADDERID] on [dbo].[' + @DATATABLENAME + '];' + char(13) +
                   char(13) +
                   'if exists(select * from dbo.sysindexes where name = ''IX_' + @DATATABLENAME + '_TESTSEGMENTID_DONORID'')' + char(13) +
                   '  drop index [IX_' + @DATATABLENAME + '_TESTSEGMENTID_DONORID] on [dbo].[' + @DATATABLENAME + '];' + char(13) +
                   char(13);

        if dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0
          set @SQL += 'if exists(select * from dbo.sysindexes where name = ''IX_' + @DATATABLENAME + '_CONSTITUENTAPPEALID'')' + char(13) +
                      '  drop index [IX_' + @DATATABLENAME + '_CONSTITUENTAPPEALID] on [dbo].[' + @DATATABLENAME + '];' + char(13) +
                      char(13);

        -- restore FINDERNUMBER column to nullable state

        set @SQL += 'if exists(select * from dbo.sysindexes where name = ''UIX_' + @DATATABLENAME + '_FINDERNUMBER'')' + char(13) +
                    '  drop index [UIX_' + @DATATABLENAME + '_FINDERNUMBER] on dbo.[' + @DATATABLENAME + '];' + char(13) +
                    'alter table dbo.[' + @DATATABLENAME + '] alter column [FINDERNUMBER] bigint null;'

        exec (@SQL);
      end
    end


  --Since we are recalculating segment counts, we can delete any constituents we may have merged on-the-fly during an export-before-activate

  --process for this mailing.  This table does not have auditing, so we can just use the 'delete' statement here.

  delete from dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS]
  where [SEGMENTATIONID] = @SEGMENTATIONID;

  return 0;