USP_MKTCONSTITUENTFILEIMPORT_CREATEDATATABLE

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

  declare @ACTIVE bit;

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

  if @ACTIVE = 0
  begin
      declare @SQL nvarchar(max);
      declare @DATATABLE nvarchar(128) = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);

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

          /* Create the table */
          set @SQL = 'create table dbo.[' + @DATATABLE + '] (' + char(13) +
                     '  [ID] uniqueidentifier not null constraint [DF_' + @DATATABLE + '_ID] default (newsequentialid()) rowguidcol,' + char(13) +
                     '  [CONSTITUENTFILEIMPORTID] uniqueidentifier not null,' + char(13) +
                     '  [SEGMENTATIONSEGMENTID] uniqueidentifier,' + char(13) +
                     '  [SEGMENTATIONTESTSEGMENTID] uniqueidentifier,' + char(13) +
                     '  [FINDERNUMBER] bigint,' + char(13) +
                     '  [SOURCECODE] nvarchar(50),' + char(13) +
                     '  constraint [PK_' + @DATATABLE + '] primary key clustered ([ID] asc)' + 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);

          /* All indexes will be created after the RECORDID field is added and the table is populated for the first time. */

        end
    end

  return 0;