USP_SEARCHCONSTITUENT_BATCHCOAUPDATE

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

  declare @NCOALASTSUBMITDATE datetime;
  declare @COAUPDATEID uniqueidentifier;

  begin try

    select
      top 1 @COAUPDATEID = [COAUPDATEID]
    from
      dbo.[BATCHCOAUPDATE]
    where
      [BATCHID] = @BATCHID;

    select
      @NCOALASTSUBMITDATE = [COAUPDATESUBMITPROCESS].[DATELASTRUN]
    from
      dbo.[COAUPDATESUBMITPROCESS]
    where
      [COAUPDATESUBMITPROCESS].[COAUPDATEID] = @COAUPDATEID;

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

      create table #SEARCHCONSTITUENTCOAUPDATE ([CONSTITID] uniqueidentifier);

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

      create index [IX_SEARCHCONSTITUENTCOAUPDATE_CONSTITID] on #SEARCHCONSTITUENTCOAUPDATE(CONSTITID);

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

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

  return 0;
end