USP_SEARCHLIST_CONSTITUENTBATCHDUPLICATELOOKUPID_2

      create procedure [dbo].[USP_SEARCHLIST_CONSTITUENTBATCHDUPLICATELOOKUPID_2]
            (
                @PRIMARYRECORDID uniqueidentifier = null,
                @LOOKUP_ID nvarchar(100) = null,
                @KEYNAME nvarchar(100) = null,
                @FIRSTNAME nvarchar(50) = null,
                @ADDRESSES xml = null,
                @CONSTITUENTTYPECODE tinyint = null,
                @MAIDENNAME nvarchar(100) = null,
                @MIDDLENAME nvarchar(50) = null,
                @PHONES xml = null,
                @EMAILADDRESSES xml = null,
                @GENDERCODE tinyint   = null, 
                @MAXROWS smallint = 100,
                @BBISTRANSACTIONTYPE nvarchar(512) = null
            )
            as
                set nocount on;                

                    declare @POSTCODE nvarchar(12), @ADDRESSBLOCK nvarchar(150), @EMPLOYER nvarchar(100) = '';

                    declare @CANDIDATE table
                    (
                        CONSTITUENTID uniqueidentifier,
                        ADDRESSID uniqueidentifier,
                        MATCHPERCENTAGE numeric(5, 2),
                        LOOKUPIDTYPE nvarchar(100),
                        LOOKUPID nvarchar(100)
                    )

                    if @PRIMARYRECORDID is null
                    begin
                        if len(@LOOKUP_ID) > 0
                            begin
                                with LOOKUPIDS_CTE as 
                                (
                                    select top(@MAXROWS)
                                        C.ID ID,
                                        'Lookup ID' LOOKUPIDTYPE,
                                        C.LOOKUPID LOOKUPID
                                    from
                                        dbo.CONSTITUENT C
                                    where
                                        LOOKUPID = @LOOKUP_ID

                                    union all

                                    select top(@MAXROWS)
                                        C.ID,
                                        ALTC.DESCRIPTION,
                                        AL.ALTERNATELOOKUPID
                                    from
                                        dbo.ALTERNATELOOKUPID AL
                                    inner join
                                        dbo.CONSTITUENT C on C.ID = AL.CONSTITUENTID
                                    left join
                                        dbo.ALTERNATELOOKUPIDTYPECODE ALTC on ALTC.ID = AL.ALTERNATELOOKUPIDTYPECODEID
                                    where
                                        AL.ALTERNATELOOKUPID = @LOOKUP_ID
                                )

                                insert into @CANDIDATE (CONSTITUENTID, ADDRESSID, MATCHPERCENTAGE, LOOKUPIDTYPE, LOOKUPID)
                                select
                                    LID.ID,
                                    DUPLICATES.ADDRESSID,
                                    DUPLICATES.MATCHPERCENTAGE,
                                    LID.LOOKUPIDTYPE LOOKUPIDTYPE,
                                    LID.LOOKUPID LOOKUPID
                                from
                                    LOOKUPIDS_CTE LID
                                left join
                                    dbo.UFN_CONSTITUENT_GETDUPLICATECANDIDATES_2(@KEYNAME, 
                                                                                @FIRSTNAME, 
                                                                                @ADDRESSES,
                                                                                @MAIDENNAME, 
                                                                                @MIDDLENAME, 
                                                                                @PHONES,
                                                                                @EMAILADDRESSES, 
                                                                                @GENDERCODE, 
                                                                                @EMPLOYER,  
                                                                                @BBISTRANSACTIONTYPE) DUPLICATES on LID.ID = DUPLICATES.CONSTITUENTID
                            end
                        else
                            begin
                                if @CONSTITUENTTYPECODE = 0 -- Only perform duplicate search for individuals
                                    begin
                                        insert into @CANDIDATE (CONSTITUENTID, ADDRESSID, MATCHPERCENTAGE, LOOKUPIDTYPE, LOOKUPID)
                                        select
                                            DUPLICATES.CONSTITUENTID,
                                            DUPLICATES.ADDRESSID,
                                            DUPLICATES.MATCHPERCENTAGE,
                                            'Lookup ID' LOOKUPIDTYPE,
                                            null
                                        from 
                                            dbo.UFN_CONSTITUENT_GETDUPLICATECANDIDATES_2(@KEYNAME, 
                                                                                        @FIRSTNAME, 
                                                                                        @ADDRESSES,
                                                                                        @MAIDENNAME, 
                                                                                        @MIDDLENAME, 
                                                                                      @PHONES,
                                                                                    @EMAILADDRESSES, 
                                                                                        @GENDERCODE, 
                                                                                        @EMPLOYER,
                                                                                        @BBISTRANSACTIONTYPE) DUPLICATES
                                    end 
                            end    
                    end    

                    select top(@MAXROWS)
                        C.ID,
                        D.MATCHPERCENTAGE,
                        C.NAME,
                        A.ADDRESSBLOCK,
                        A.CITY,
                        S.ABBREVIATION,
                        A.POSTCODE,
                        D.LOOKUPIDTYPE,
                        coalesce(D.LOOKUPID, C.LOOKUPID) as LOOKUPID,
                        C.DATECHANGED
                    from 
                        @CANDIDATE D
                        inner join dbo.CONSTITUENT C on D.CONSTITUENTID = C.ID
                        left join dbo.ADDRESS A on D.ADDRESSID = A.ID
                        left join dbo.STATE S on A.STATEID = S.ID
                    order by 
                        D.MATCHPERCENTAGE desc,
                        C.KEYNAME,
                        C.FIRSTNAME