USP_MKTSEGMENTWHITEMAILREFRESH_CREATEGIFTSTABLE

CREATE procedure dbo.[USP_MKTSEGMENTWHITEMAILREFRESH_CREATEGIFTSTABLE]
(
  @RECORDSOURCEID uniqueidentifier,
  @TABLENAME 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);

  begin try
    set @RETVAL = 0;
    set @TABLENAME = 'MKTSEGMENTWHITEMAILDATA_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;

    set @SQL = 'create table dbo.[' + @TABLENAME + '] (' + char(13) +
               '  [ID] ' + @GIFTIDDATATYPE + ' not null,' + char(13) +
               '  [DONORID] ' + @DONORIDDATATYPE + ' not null,' + char(13) +
               '  [AMOUNT] money not null,' + char(13) +
               '  [ORGANIZATIONAMOUNT] money not null,' + char(13) +
               '  [DATE] datetime not null' + char(13) +
               '  constraint [PK_' + @TABLENAME + '] primary key clustered ([ID] asc) with (PAD_INDEX = off, IGNORE_DUP_KEY = off) on [DEFGROUP]' + char(13) +
               ') on [DEFGROUP]';

    if serverproperty('engineedition') = 3
      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.[' + @TABLENAME + '] to BBAPPFXSERVICEROLE';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_DONORID] on dbo.[' + @TABLENAME + '] ([DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off) on [IDXGROUP]';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_DATE] on dbo.[' + @TABLENAME + '] ([DATE] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off) on [IDXGROUP]';
    exec (@SQL);
  end try

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

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

  return @RETVAL;