USP_MKTUPDATEMAILINGCOUNTS_VALIDATEIMPORTTABLE

CREATE procedure dbo.[USP_MKTUPDATEMAILINGCOUNTS_VALIDATEIMPORTTABLE]
(
  @ID uniqueidentifier,
  @IMPORTTABLENAME nvarchar(128)
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @DATATABLENAME nvarchar(128);
  declare @CHECKDIGIT bit;
  declare @SQL nvarchar(max);

  select
    @SEGMENTATIONID = [SEGMENTATIONID],
    @DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([SEGMENTATIONID]),
    @CHECKDIGIT = (case when exists(select * from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [SEGMENTATIONID] = [MKTUPDATEMAILINGCOUNTSPROCESS].[SEGMENTATIONID] and [CHECKDIGIT] = 1) then 1 else 0 end)
  from dbo.[MKTUPDATEMAILINGCOUNTSPROCESS]
  where [ID] = @ID;

  --Add an index on the FINDERNUMBER column to make the validation below faster...

  set @SQL = 'create nonclustered index [IX_' + replace(@IMPORTTABLENAME, '#', '') + '_FINDERNUMBER] on dbo.[' + @IMPORTTABLENAME + '] ([FINDERNUMBER] asc)';
  exec (@SQL);

  --Exception: InvalidFinderNumber - Finder number format is invalid

  set @SQL = 'update dbo.[' + @IMPORTTABLENAME + '] set' + char(13) +
             '  [EXCEPTIONCODE] = 16' + char(13) +
             'where [FINDERNUMBER] like ''%[^0-9]%''' + char(13) +
             'and [EXCEPTIONCODE] = 0';
  exec (@SQL);

  --Exception: BadFinderNumber - Finder number not in marketing effort. (Part 1 of 2)

  --Yes, we are checking this exception twice, but two different ways...

  --This way just checks that the finder number is within the proper ranges so that we can properly validate the check-digits next.

  set @SQL = 'update dbo.[' + @IMPORTTABLENAME + '] set' + char(13) +
             '  [EXCEPTIONCODE] = 3' + char(13) +
             'from dbo.[' + @IMPORTTABLENAME + '] as [EXCLUDE]' + char(13) +
             'left join dbo.[MKTSEGMENTATIONFINDERNUMBER] on ' + (case when @CHECKDIGIT = 1 then '(cast([EXCLUDE].[FINDERNUMBER] as bigint) / 10)' else '[EXCLUDE].[FINDERNUMBER]' end) + ' between [MKTSEGMENTATIONFINDERNUMBER].[MIN] and [MKTSEGMENTATIONFINDERNUMBER].[MAX] and [MKTSEGMENTATIONFINDERNUMBER].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
             'where [MKTSEGMENTATIONFINDERNUMBER].[ID] is null' + char(13) +
             'and [EXCLUDE].[EXCEPTIONCODE] = 0';
  exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;

  --Exception: FailedCheckDigit - Failed check digit.

  if @CHECKDIGIT = 1
    begin
      set @SQL = 'update dbo.[' + @IMPORTTABLENAME + '] set' + char(13) +
                 '  [EXCEPTIONCODE] = 2' + char(13) +
                 'where [EXCEPTIONCODE] = 0' + char(13) +
                 'and dbo.[UFN_MKTFINDERNUMBER_VALIDATE_MOD10]([FINDERNUMBER]) = 0';
      exec (@SQL);
    end

  --Exception: BadFinderNumber - Finder number not in marketing effort. (Part 2 of 2)

  --Yes, we are checking this exception twice, but two different ways...

  --This way just checks that the finder number is actually in the mailing data table so that it can be removed.

  set @SQL = 'update dbo.[' + @IMPORTTABLENAME + '] set' + char(13) +
             '  [EXCEPTIONCODE] = 3' + char(13) +
             'from dbo.[' + @IMPORTTABLENAME + '] as [EXCLUDE]' + char(13) +
             'left join dbo.[' + @DATATABLENAME + '] as [DATA] on [DATA].[FINDERNUMBER] = cast([EXCLUDE].[FINDERNUMBER] as bigint)' + char(13) +
             'left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DATA].[SEGMENTID]' + char(13) +
             'where ([DATA].[FINDERNUMBER] is null or [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1)' + char(13) +
             'and [EXCLUDE].[EXCEPTIONCODE] = 0';
  exec (@SQL);

  --Exception: DuplicateFinderNumber - Duplicate finder number imported.

  set @SQL = 'with [INVALID] ([FINDERNUMBER]) as' + char(13) +
             '(' + char(13) +
             '  select [FINDERNUMBER]' + char(13) +
             '  from dbo.[' + @IMPORTTABLENAME + ']' + char(13) +
  '  group by [FINDERNUMBER]' + char(13) +
             '  having count([FINDERNUMBER]) > 1' + char(13) +
             ')' + char(13) +
             'update dbo.[' + @IMPORTTABLENAME + '] set' + char(13) +
             '  [EXCEPTIONCODE] = 1' + char(13) +
             'from dbo.[' + @IMPORTTABLENAME + '] as [EXCLUDE]' + char(13) +
             'inner join [INVALID] on [INVALID].[FINDERNUMBER] = [EXCLUDE].[FINDERNUMBER]' + char(13) +
             'where [EXCLUDE].[EXCEPTIONCODE] = 0';
  exec (@SQL);

  return 0;