USP_SEARCHCONSTITUENT_BATCHADDRESSVALIDATION

create procedure dbo.USP_SEARCHCONSTITUENT_BATCHADDRESSVALIDATION
(
  @BATCHID uniqueidentifier
)
as
begin
  set nocount on;

  declare @VALIDATIONASTRUNDATE datetime;
  declare @ADDRESSVALIDATIONID uniqueidentifier;

  begin try

    select
      top 1 @ADDRESSVALIDATIONID = [ID]
    from
      dbo.[ADDRESSVALIDATION]
    where
      [BATCHID] = @BATCHID;

    select
      @VALIDATIONASTRUNDATE = [ADDRESSVALIDATIONPROCESS].[DATELASTRUN]
    from
      dbo.[ADDRESSVALIDATIONPROCESS]
    where
      [ADDRESSVALIDATIONPROCESS].[ADDRESSVALIDATIONID] = @ADDRESSVALIDATIONID;

    if @VALIDATIONASTRUNDATE is not null
    begin
      if object_id('tempdb..#SEARCHCONSTITUENTADDRESSVALIDATION') is not null
        drop table #SEARCHCONSTITUENTADDRESSVALIDATION;

      create table #SEARCHCONSTITUENTADDRESSVALIDATION ([CONSTITID] uniqueidentifier);

      insert into #SEARCHCONSTITUENTADDRESSVALIDATION
      select
        distinct [ADDRESS].[CONSTITUENTID]
      from
        dbo.[ADDRESSVALIDATIONUPDATE]
        inner join dbo.[ADDRESS] on [ADDRESS].[ID] = [ADDRESSVALIDATIONUPDATE].[ID]
      where
       [ADDRESSVALIDATIONUPDATE].[LASTVALIDATIONATTEMPTDATE] = @VALIDATIONASTRUNDATE;

      create index [IX_SEARCHCONSTITUENTADDRESSVALIDATION_CONSTITID] on #SEARCHCONSTITUENTADDRESSVALIDATION(CONSTITID);

      exec dbo.[USP_POPULATESEARCHCONSTITUENT_FROMTABLE] '#SEARCHCONSTITUENTADDRESSVALIDATION', 'CONSTITID';

      if object_id('tempdb..#SEARCHCONSTITUENTADDRESSVALIDATION') is not null
        drop table #SEARCHCONSTITUENTADDRESSVALIDATION;
    end
  end try
  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;
end