USP_MKTSEGMENTATIONACTIVATE_CREATEGIFTSTABLE

CREATE procedure dbo.USP_MKTSEGMENTATIONACTIVATE_CREATEGIFTSTABLE
(
  @RECORDSOURCEID uniqueidentifier,
  @NORMALGIFTS bit = 1,
  @GIFTSTABLENAME nvarchar(128) = null output
)
with execute as owner
as
  set nocount on;

  declare @RETVAL int;
  declare @SQL nvarchar(max);
  declare @GIFTIDDATATYPE nvarchar(128);
  declare @DONORIDDATATYPE nvarchar(128);
  declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

  begin try
    set @RETVAL = 0;
    set @GIFTSTABLENAME = 'MKTSEGMENTATIONDATA_' + (case when @NORMALGIFTS = 1 then 'NORMAL' else 'UNRESOLVED' end) + 'GIFTS_' + replace(convert(nvarchar(36), NewID()), '-', '_');

    select
      @DONORIDDATATYPE = [CONSTITQV].[PRIMARYKEYTYPENAME],
      @GIFTIDDATATYPE = [GIFTQV].[PRIMARYKEYTYPENAME]
    from dbo.[MKTGIFTRECORDSOURCE]
    inner join dbo.[QUERYVIEWCATALOG] as [CONSTITQV] on [CONSTITQV].[ID] = [MKTGIFTRECORDSOURCE].[ID]
    inner join dbo.[QUERYVIEWCATALOG] as [GIFTQV] on [GIFTQV].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
    where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;


    /* Create the table */
    set @SQL = 'create table dbo.[' + @GIFTSTABLENAME + '] (' + char(13) +
               '  [ID] ' + @GIFTIDDATATYPE + ' not null,' + char(13) +
               '  [DONORID] ' + @DONORIDDATATYPE + ' not null,' + char(13) +
               '  [AMOUNT] money not null,' + char(13) +
               '  [DATE] datetime not null,' + char(13) +
               '  [SOURCECODE] nvarchar(50) null,' + char(13) +
               '  [FINDERNUMBER] bigint null,' + char(13) +
               '  [ORGANIZATIONAMOUNT] money not null' + char(13) +
               '  constraint [PK_' + @GIFTSTABLENAME + '] primary key clustered ([ID] asc) with (PAD_INDEX = off, IGNORE_DUP_KEY = off, FILLFACTOR = 100) on [DEFGROUP]' + char(13) +
               ') on [DEFGROUP]';

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

    exec (@SQL);

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

    /* We only insert into this table one time and then truncate, so we can use a fillfactor of 100% on the indexes */
    set @SQL = 'create nonclustered index [IX_' + @GIFTSTABLENAME + '_DONORID] on dbo.[' + @GIFTSTABLENAME + '] ([DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @GIFTSTABLENAME + '_DATE] on dbo.[' + @GIFTSTABLENAME + '] ([DATE] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @GIFTSTABLENAME + '_SOURCECODE] on dbo.[' + @GIFTSTABLENAME + '] ([SOURCECODE] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @GIFTSTABLENAME + '_FINDERNUMBER] on dbo.[' + @GIFTSTABLENAME + '] ([FINDERNUMBER] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    set @RETVAL = 1;

    if @GIFTSTABLENAME is not null and len(@GIFTSTABLENAME) > 0 and exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @GIFTSTABLENAME)
      begin
        set @SQL = 'drop table dbo.[' + @GIFTSTABLENAME + ']';
        exec (@SQL);
      end
  end catch

  return @RETVAL;