USP_MKTSEGMENTATIONACTIVATE_CREATEFINDERTABLE

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

  declare @ACTIVE bit;
  declare @MAILINGTYPECODE tinyint;

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

  if @ACTIVE = 0
    begin
      declare @SQL nvarchar(max);
      declare @DATATABLE nvarchar(128);
      declare @FINDERTABLE nvarchar(128);
      declare @DONORIDDATATYPE nvarchar(128);
      declare @DONORCOLLATIONHINT nvarchar(50);
      declare @ADDITIONALIDDATATYPE nvarchar(128);
      declare @ADDITIONALCOLLATIONHINT nvarchar(50);

      set @FINDERTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKEFINDERTABLENAME](@SEGMENTATIONID);

      set @DONORIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID);

      if @DONORIDDATATYPE like '%char%'
        set @DONORCOLLATIONHINT = ' collate database_default'
      else
        set @DONORCOLLATIONHINT = '';

      if @MAILINGTYPECODE in (1, 2, 3, 5) -- acknowledgement, membership, sponsorship, communication revenue

        begin
          set @ADDITIONALIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);

          if @ADDITIONALIDDATATYPE like '%char%'
            set @ADDITIONALCOLLATIONHINT = ' collate database_default'
          else
            set @ADDITIONALCOLLATIONHINT = ''
        end

      --drop the table it it already exists as we need to recreate it

      set @SQL = 'if object_id(''tempdb..##' + @FINDERTABLE + ''') is not null' + char(13) +
                  '  drop table dbo.[##' + @FINDERTABLE + '];';
      exec (@SQL);

      /* Create the table */
      set @SQL = 'create table dbo.[##' + @FINDERTABLE + '] (' + char(13) + 
                 '  [SEGMENTID] uniqueidentifier not null,' + char(13) +
                 '  [EXCLUDE] bit not null default 0,' + char(13) +
                 '  [FINDERNUMBER] bigint null,' + char(13) +
                 '  [ROWNUMBER] bigint null,' + char(13) +
                 '  [DONORQUERYVIEWCATALOGID] uniqueidentifier not null,' + char(13) +
                 (case when @MAILINGTYPECODE in (1, 5) then '  [REVENUEID] ' + @ADDITIONALIDDATATYPE + @ADDITIONALCOLLATIONHINT + ' null,' + char(13) else '' end) +
                 (case when @MAILINGTYPECODE = 2 then '  [MEMBERSHIPID] ' + @ADDITIONALIDDATATYPE + @ADDITIONALCOLLATIONHINT + ' null,' + char(13) else '' end) +
                 (case when @MAILINGTYPECODE = 3 then '  [SPONSORSHIPID] ' + @ADDITIONALIDDATATYPE + @ADDITIONALCOLLATIONHINT + ' null,' + char(13) else '' end) +
                 '  [DONORID] ' + @DONORIDDATATYPE + @DONORCOLLATIONHINT + ' not null' + char(13) +
                 ')';
      exec (@SQL);

      /* Create a unique index on the DonorID fields */
      if @MAILINGTYPECODE in (1, 5)
        set @SQL = 'create clustered index [IX_' + @FINDERTABLE + '_DONORQUERYVIEWCATALOGID_REVENUEID_DONORID] on [dbo].[##' + @FINDERTABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [REVENUEID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off)';
      else if @MAILINGTYPECODE = 2
        set @SQL = 'create clustered index [IX_' + @FINDERTABLE + '_DONORQUERYVIEWCATALOGID_MEMBERSHIPID_DONORID] on [dbo].[##' + @FINDERTABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [MEMBERSHIPID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off)';
      else if @MAILINGTYPECODE = 3
        set @SQL = 'create clustered index [IX_' + @FINDERTABLE + '_DONORQUERYVIEWCATALOGID_SPONSORSHIPID_DONORID] on [dbo].[##' + @FINDERTABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [SPONSORSHIPID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off)';
    else
        set @SQL = 'create clustered index [IX_' + @FINDERTABLE + '_DONORQUERYVIEWCATALOGID_DONORID] on [dbo].[##' + @FINDERTABLE + '] ([DONORQUERYVIEWCATALOGID] asc, [DONORID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off)';

      exec (@SQL);

      set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

      if exists(select top 1 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = @DATATABLE)
        begin
          set @SQL = 'insert into dbo.[##' + @FINDERTABLE + '] (' + char(13) + 
                     '  [SEGMENTID],' + char(13) +
                     '  [EXCLUDE],' + char(13) +
                     '  [FINDERNUMBER],' + char(13) +
                     '  [DONORQUERYVIEWCATALOGID],' + char(13) +
                     (case when @MAILINGTYPECODE in (1, 5) then '  [REVENUEID],' + char(13) else '' end) +
                     (case when @MAILINGTYPECODE = 2 then '  [MEMBERSHIPID],' + char(13) else '' end) +
                     (case when @MAILINGTYPECODE = 3 then '  [SPONSORSHIPID],' + char(13) else '' end) +
                     '  [DONORID]' + char(13) +
                     ')' + char(13) +          
                     'select' + char(13) +
                     '  [DATA].[SEGMENTID],' + char(13) +
                     '  [MKTSEGMENTATIONSEGMENT].[EXCLUDE],' + char(13) +
                     '  [DATA].[FINDERNUMBER],' + char(13) +
                     '  [DATA].[DONORQUERYVIEWCATALOGID],' + char(13) +
                     (case when @MAILINGTYPECODE in (1, 5) then '  [DATA].[REVENUEID],' + char(13) else '' end) +
                     (case when @MAILINGTYPECODE = 2 then '  [DATA].[MEMBERSHIPID],' + char(13) else '' end) +
                     (case when @MAILINGTYPECODE = 3 then '  [DATA].[SPONSORSHIPID],' + char(13) else '' end) +
                     '  [DATA].[DONORID]' + char(13) +
                     'from dbo.[' + @DATATABLE + '] as [DATA]' + char(13) +
                     'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DATA].[SEGMENTID];';
          exec (@SQL);
        end
    end

  return 0;