USP_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_CREATETABLE

create procedure dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_CREATETABLE]
(
  @SEGMENTATIONID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @TABLENAME nvarchar(128);

  begin try
    set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);

    -- if the table is not yet created, then create it

    if not exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
      begin
        declare @SQL nvarchar(max);
        declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

        -- donor ID type is always uniqueidentifier because address processing is only valid for 

        -- BBEC record source

        set @SQL = 'create table dbo.[' + @TABLENAME + '](' + char(13) +
                   '  [SEGMENTID] uniqueidentifier not null,' + char(13) +
                   '  [CONSTITUENTID] uniqueidentifier not null,' + char(13) +
                   '  [EMAILADDRESS] nvarchar(100) null, -- can''t use UDT_EMAILADDRESS in temp' + char(13) +

                   '  [CONTACTID] uniqueidentifier null' + char(13) +
                   ') on [DEFGROUP]';

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

        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_SEGMENTID] on dbo.[' + @TABLENAME + '] ([SEGMENTID] asc) include ([CONSTITUENTID]) with (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_CONSTITUENTID] on dbo.[' + @TABLENAME + '] ([CONSTITUENTID] asc) include ([SEGMENTID]) with (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
        exec (@SQL);

        set @SQL = 'grant select, insert, delete on dbo.[' + @TABLENAME + '] to BBAPPFXSERVICEROLE';
        exec (@SQL);
      end
  end try

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

  return 0;