USP_MKTSEGMENTATIONACTIVATE_CREATETEMPSEGMENTTABLE

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_CREATETEMPSEGMENTTABLE]
(
  @SEGMENTID uniqueidentifier,
  @TEMPSEGMENTTABLENAME nvarchar(128) = null output
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILINGTYPECODE tinyint;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @DONORIDDATATYPE nvarchar(128);
  declare @REVENUEIDDATATYPE nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    select
      @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
      @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
      @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

    set @DONORIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID)
    if @MAILINGTYPECODE in (1, 5)
      set @REVENUEIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID)

    if len(isnull(@TEMPSEGMENTTABLENAME,'')) = 0
      set @TEMPSEGMENTTABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETEMPSEGMENTTABLENAME](@SEGMENTID);

    if exists(select 1 from [TEMPDB].[INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TEMPSEGMENTTABLENAME) or exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TEMPSEGMENTTABLENAME)
      exec ('drop table dbo.[' + @TEMPSEGMENTTABLENAME + ']');

    /* Create the temp segment table */
    set @SQL = 'create table [dbo].[' + @TEMPSEGMENTTABLENAME + '] (' + char(13) +
               '  [ID] ' + (case when @MAILINGTYPECODE in (1, 5) then @REVENUEIDDATATYPE else @DONORIDDATATYPE end) + ' not null,' + char(13) +
               (case when @MAILINGTYPECODE in (1, 5) and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1 then '[DONORID] ' + @DONORIDDATATYPE + ' not null,' + char(13) else '' end) + 
               '  [ROW] [int] not null identity (1, 1)' +
               (case when @MAILINGTYPECODE in (1, 5) then '' else ',' + char(13) + '  constraint [PK_' + replace(@TEMPSEGMENTTABLENAME, '#', '') + '] primary key clustered ([ID] asc) with (PAD_INDEX = off, IGNORE_DUP_KEY = off)' end) + char(13) +
               ');'
               + char(13) +
               'create unique nonclustered index [IX_' + replace(@TEMPSEGMENTTABLENAME, '#', '') + '_ROW] on [dbo].[' + @TEMPSEGMENTTABLENAME + '] ([ROW] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off);';
    exec (@SQL);
  end try

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

  return 0;