USP_POPULATESEARCHCONSTITUENT

CREATE procedure dbo.USP_POPULATESEARCHCONSTITUENT (
  @ROWSCREATED int = null output
) as
-- Performs the initial population, or complete repopulation of the SEARCHCONSTITUENT table

-- used by the new constituent matching algorithm.

--

-- SEARCHCONSTITUENT is truncated as part of this procedure.  This should never be done on an

-- active database.

begin
  begin try
    truncate table dbo.SEARCHCONSTITUENT;

    -- drop indexes on the table for improved performance

    declare @INDEXES table (INDEXNAME nvarchar(128))

    insert into @INDEXES
    select NAME
    from sys.indexes
    where object_id=object_id('SEARCHCONSTITUENT')
    and TYPE <> 1

    declare @INDEXNAME nvarchar(128)

    while (select count(*) from @INDEXES) > 0
    begin
      select @INDEXNAME = INDEXNAME from @INDEXES;
      exec ('drop index ' + @INDEXNAME + ' on dbo.SEARCHCONSTITUENT')
      delete from @INDEXES where INDEXNAME = @INDEXNAME;
    end

    -- repopulate the table for all constituents

    exec dbo.USP_INSERTSEARCHCONSTITUENT @ROWSCREATED = @ROWSCREATED output

    -- recreate indexes

    declare @XML xml
    select @XML = TABLESPECXML
    from dbo.TABLECATALOG
    where TABLENAME = 'SEARCHCONSTITUENT'

    declare @CHANGEAGENTID uniqueidentifier
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    exec dbo.USP_LOADSPEC @SPECXML = @XML, @CHANGEAGENTID = @CHANGEAGENTID, @SOURCENAME = '', @ITEMNAME = '', @FORCERELOAD = true
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end