USP_REVENUESEGMENT_CREATETABLE

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

  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    set @TABLENAME = dbo.[UFN_REVENUESEGMENT_MAKETABLENAME](@QUERYVIEWCATALOGID);

    /* 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 @PRIMARYKEYTYPENAME nvarchar(20);
        declare @FKCONSTRAINT nvarchar(255);
        declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

        /* If this is BBEC, and the BBEC record source, then we know the primary key type and we can create a foreign key relationship to the revenue table */
        if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@QUERYVIEWCATALOGID) = 1
          begin
            set @PRIMARYKEYTYPENAME = 'uniqueidentifier';
            set @FKCONSTRAINT = '  constraint [FK_' + @TABLENAME + '_REVENUE] foreign key ([REVENUEID]) references dbo.[FINANCIALTRANSACTION] ([ID]) on delete cascade,' + char(13);
          end
        else
          begin
            /* This is a generic query view source, so get the primary key type and do not set the foreign key relationship */
            select
              @PRIMARYKEYTYPENAME = [PRIMARYKEYTYPENAME],
              @FKCONSTRAINT = ''
            from dbo.[QUERYVIEWCATALOG]
            where [ID] = @QUERYVIEWCATALOGID;
          end

        set @SQL = 'create table dbo.[' + @TABLENAME + '](' + char(13) +
                   '  [REVENUEID] ' + @PRIMARYKEYTYPENAME + ' not null,' + char(13) +
                   '  [SEGMENTID] uniqueidentifier not null,' + char(13) +
                   '  [TESTSEGMENTID] uniqueidentifier,' + char(13) +
                   '  [SOURCECODEMAPID] uniqueidentifier,' + char(13) +
                   '  constraint [PK_' + @TABLENAME + '] primary key clustered ([REVENUEID] asc) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) on [TRANGROUP],' + char(13) +
                   @FKCONSTRAINT +
                   '  constraint [FK_' + @TABLENAME + '_MKTSEGMENTATIONSEGMENT] foreign key ([SEGMENTID]) references dbo.[MKTSEGMENTATIONSEGMENT] ([ID]) on delete cascade,' + char(13) +
                   '  constraint [FK_' + @TABLENAME + '_MKTSEGMENTATIONTESTSEGMENT] foreign key ([TESTSEGMENTID]) references dbo.[MKTSEGMENTATIONTESTSEGMENT] ([ID]) on delete no action,' + char(13) +
                   '  constraint [FK_' + @TABLENAME + '_MKTSOURCECODEMAP] foreign key ([SOURCECODEMAPID]) references dbo.[MKTSOURCECODEMAP] ([ID]) on delete no action' + char(13) +
                   ') on [TRANGROUP]';

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

        exec (@SQL);

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

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_TESTSEGMENTID] on dbo.[' + @TABLENAME + '] ([TESTSEGMENTID] asc) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [TRANIDXGROUP]';
        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_SOURCECODEMAPID] on dbo.[' + @TABLENAME + '] ([SOURCECODEMAPID] asc) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [TRANIDXGROUP]';
        exec (@SQL);

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

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

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

  return 0;