USP_CONSTITUENTDUPLICATESEARCHPROCESS_PREPARERESULTSTABLE

Readies the results table for the constituent duplicate search process.
Parameters

Parameter	Parameter Type	Mode
@ISINCREMENTAL	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
@INCLUDEINACTIVE	bit	IN
Definition

Copy


CREATE procedure dbo.USP_CONSTITUENTDUPLICATESEARCHPROCESS_PREPARERESULTSTABLE
(
    @ISINCREMENTAL bit = 0,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @INCLUDEINACTIVE bit = 1
)
with execute as owner
as
begin

    set nocount on;

    if @ISINCREMENTAL = 0
    begin
        truncate table dbo.[CONSTITUENTDUPLICATESEARCHPROCESSFULLRESULTS]
        dbcc checkident ('dbo.[CONSTITUENTDUPLICATESEARCHPROCESSFULLRESULTS]', reseed, 0)

    truncate table dbo.[CONSTITUENTDUPLICATESEARCHPROCESSFULLRESULTS_RAWDATA]
        dbcc checkident ('dbo.[CONSTITUENTDUPLICATESEARCHPROCESSFULLRESULTS_RAWDATA]', reseed, 0)
    end
    else
    begin
        --Delete records that no longer exist in the database.

        delete from dbo.CONSTITUENTDUPLICATESEARCHPROCESSINCREMENTALRESULTS
        where 
            not exists
            (
                select 1 from dbo.CONSTITUENT
                where 
                    ID = CONSTITUENTDUPLICATESEARCHPROCESSINCREMENTALRESULTS.CONSTITID and
                    (
                        @INCLUDEINACTIVE = 1 or 
                        ISINACTIVE = 0
                    )
            ) or
            not exists
            (
                select 1 from dbo.CONSTITUENT
                where 
                    ID = CONSTITUENTDUPLICATESEARCHPROCESSINCREMENTALRESULTS._key_CONSTITID and
                    (
                        @INCLUDEINACTIVE = 1 or 
                        ISINACTIVE = 0
                    )
            )

      --Delete records that no longer exist in the database.

        delete from dbo.CONSTITUENTDUPLICATESEARCHPROCESSINCREMENTALRESULTS_RAWDATA
        where 
            not exists
            (
                select 1 from dbo.CONSTITUENT
                where 
                    ID = CONSTITUENTDUPLICATESEARCHPROCESSINCREMENTALRESULTS_RAWDATA.CONSTITID and
                    (
                        @INCLUDEINACTIVE = 1 or 
                        ISINACTIVE = 0
                    )
            ) or
            not exists
            (
                select 1 from dbo.CONSTITUENT
                where 
                    ID = CONSTITUENTDUPLICATESEARCHPROCESSINCREMENTALRESULTS_RAWDATA._key_CONSTITID and
                    (
                        @INCLUDEINACTIVE = 1 or 
                        ISINACTIVE = 0
                    )
            )
    end

end