USP_MKTCONSTITUENTFILEIMPORT_MATCHWITHSEGMENT

CREATE procedure dbo.[USP_MKTCONSTITUENTFILEIMPORT_MATCHWITHSEGMENT]
(
  @CONSTITUENTFILEIMPORTID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @DATATABLE nvarchar(128);
  declare @SQL nvarchar(max);

  begin try  
    select
      @SEGMENTATIONID = [SEGMENTATIONID],
      @DATATABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME]([SEGMENTATIONID])
    from dbo.[MKTCONSTITUENTFILEIMPORTPROCESS]
    where [ID] = @CONSTITUENTFILEIMPORTID;


    /* Validate source code and assign segmentid */
    /* Since there is no source code map table created, must create a temp table relating segment ids to source code */
    create table #TEMPSOURCECODETABLE (
      [SEGMENTATIONSEGMENTID] uniqueidentifier not null,
      [SEGMENTATIONTESTSEGMENTID] uniqueidentifier,
      [SOURCECODE] nvarchar(50) collate database_default
    );

    /* Get all segments and test segments with their source codes. */
    insert into #TEMPSOURCECODETABLE ([SEGMENTATIONSEGMENTID], [SEGMENTATIONTESTSEGMENTID], [SOURCECODE])
      select
        [ID],
        null,
        dbo.[UFN_MKTSOURCECODE_BUILDCODE]([ID], null, null)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      where [SEGMENTATIONID] = @SEGMENTATIONID

      union all

      select
        [MKTSEGMENTATIONSEGMENT].[ID],
        [MKTSEGMENTATIONTESTSEGMENT].[ID],
        dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONTESTSEGMENT].[ID], null, null)
      from dbo.[MKTSEGMENTATIONTESTSEGMENT]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;


    /* Match the import file to the segments and test segments based on sourcecode. */
    set @SQL =  'update dbo.[' + @DATATABLE + '] set' + char(13) +
                '  [SEGMENTATIONSEGMENTID] = [TEMP].[SEGMENTATIONSEGMENTID],' + char(13) +
                '  [SEGMENTATIONTESTSEGMENTID] = [TEMP].[SEGMENTATIONTESTSEGMENTID]' + char(13) +
                'from dbo.[' + @DATATABLE + '] as [DATA]' + char(13) +
                'left join #TEMPSOURCECODETABLE as [TEMP] on [TEMP].[SOURCECODE] = [DATA].[SOURCECODE]' + char(13) +
                'where [DATA].[CONSTITUENTFILEIMPORTID] = @CONSTITUENTFILEIMPORTID';
    exec sp_executesql @SQL, N'@CONSTITUENTFILEIMPORTID uniqueidentifier', @CONSTITUENTFILEIMPORTID = @CONSTITUENTFILEIMPORTID;

    drop table #TEMPSOURCECODETABLE;
  end try

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

  return 0;