USP_MKTSEGMENTATIONACTIVATE_CREATEDATATABLE

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

  declare @ACTIVE bit;
  declare @MAILINGTYPECODE tinyint;

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

  if @ACTIVE = 0 or @ISEXCLUSION = 1
    begin
      declare @SQL nvarchar(max);
      declare @DATATABLE nvarchar(128);
      declare @DONORIDDATATYPE nvarchar(128);
      declare @ADDITIONALIDDATATYPE nvarchar(128);
      declare @ISBBEC bit;
      declare @USECOMPRESSION bit;

      if @ISEXCLUSION = 1
        set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@SEGMENTATIONID);
      else
        set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

      if not exists(select 1 from INFORMATION_SCHEMA.TABLES where (TABLE_SCHEMA = 'dbo') and (TABLE_NAME = @DATATABLE))
        begin
          set @DONORIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID);
          if @MAILINGTYPECODE in (1, 2, 3, 5) -- acknowledgement, membership, communication revenue

            set @ADDITIONALIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);

          set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
          set @USECOMPRESSION = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

          /* Create the table */
          set @SQL = 'create table [dbo].[' + @DATATABLE + '] (' + char(13);

          if @ISEXCLUSION = 1
            set @SQL = @SQL + '  [SEGMENTATIONEXCLUSIONID] uniqueidentifier null,' + char(13) +
                              '  [FINDERNUMBER] bigint not null ,' + char(13);
          else
            set @SQL = @SQL + '  [FINDERNUMBER] bigint null,' + char(13);

          set @SQL = @SQL + 
                     '  [SEGMENTID] uniqueidentifier not null,' + char(13) +
                     '  [TESTSEGMENTID] uniqueidentifier null,' + char(13) +
                     (case when @MAILINGTYPECODE in (1, 5) then '  [REVENUEID] ' + @ADDITIONALIDDATATYPE + ' null,' + char(13) else '' end) +
                     (case when @MAILINGTYPECODE = 2 then '  [MEMBERSHIPID] ' + @ADDITIONALIDDATATYPE + ' null,' + char(13) else '' end) +
                     (case when @MAILINGTYPECODE = 3 then '  [SPONSORSHIPID] ' + @ADDITIONALIDDATATYPE + ' null,' + char(13) else '' end) +
                     '  [DONORID] ' + @DONORIDDATATYPE + ' not null,' + char(13) +
                     '  [DONORQUERYVIEWCATALOGID] uniqueidentifier null,' + char(13) +
                     '  [SOURCECODE] nvarchar(50) null,' + char(13) +
                     '  [ASKLADDERID] uniqueidentifier null,' + char(13) +
                     '  [ENTRYAMOUNT] money null' +
                     (case when @ISBBEC = 1 then ',' + char(13) + '  [CONSTITUENTAPPEALID] uniqueidentifier null' else '' end) + char(13) +
                     ') on [DEFGROUP]';

          if @USECOMPRESSION = 1
            set @SQL = @SQL + ' WITH (DATA_COMPRESSION = PAGE)';

          exec (@SQL);

          /* Grant select, insert, update, and alter rights on the new table */
          set @SQL = 'grant select, insert, update, alter, delete on dbo.[' + @DATATABLE + '] to BBAPPFXSERVICEROLE';
          exec (@SQL);

          /* Grant select rights on the new table for reports*/
          set @SQL = 'grant select on dbo.[' + @DATATABLE + '] to BBAPPFXREPORTROLE';
          exec (@SQL);

          /* Create a unique index on the DonorQueryViewCatalogID and DonorID fields */
          /* Moved this one index to before the activation process in order to speed up each segment activate. */
          /* DO NOT MOVE THIS INDEX TO THE END OF THE ACTIVATION, IT WILL SLOW DOWN SIGNIFICANTLY!!! */
          if @MAILINGTYPECODE in (1, 5)
            set @SQL = 'create clustered index [IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_REVENUEID_DONORID] on [dbo].[' + @DATATABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [REVENUEID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [DEFGROUP]';
          else if @MAILINGTYPECODE = 2
            set @SQL = 'create clustered index [IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_MEMBERSHIPID_DONORID] on [dbo].[' + @DATATABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [MEMBERSHIPID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [DEFGROUP]';
          else if @MAILINGTYPECODE = 3
            set @SQL = 'create clustered index [IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_SPONSORSHIPID_DONORID] on [dbo].[' + @DATATABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [SPONSORSHIPID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [DEFGROUP]';
          else
            set @SQL = 'create clustered index [IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_DONORID] on [dbo].[' + @DATATABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [DEFGROUP]';

          exec (@SQL);
        end
    end

  return 0;