USP_SEARCHLIST_PROSPECTRESEARCHBATCHINDIVIDUALDUPLICATELOOKUPID

            CREATE procedure dbo.USP_SEARCHLIST_PROSPECTRESEARCHBATCHINDIVIDUALDUPLICATELOOKUPID
            (
                @PRIMARYRECORDID uniqueidentifier = null,
                @LOOKUP_ID nvarchar(100) = null,
                @MAXROWS smallint = 500
            )
            as
                set nocount on;

                -- Ignore search when a constituent has been selected.

                if @PRIMARYRECORDID is null
                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 and ISORGANIZATION = 0 and ISGROUP = 0
                        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 and C.ISORGANIZATION = 0 and C.ISGROUP = 0
                        left join
                            dbo.ALTERNATELOOKUPIDTYPECODE ALTC on ALTC.ID = AL.ALTERNATELOOKUPIDTYPECODEID
                        where
                            AL.ALTERNATELOOKUPID = @LOOKUP_ID
                    ), FINAL_CTE as (
                    select distinct
                        C.ID ID,
                        C.NAME NAME,
                        A.ADDRESSBLOCK ADDRESSBLOCK,
                        A.CITY CITY,
                        S.ABBREVIATION STATE,
                        A.POSTCODE POSTCODE,
                        LID.LOOKUPIDTYPE LOOKUPIDTYPE,
                        LID.LOOKUPID LOOKUPID,
                        C.KEYNAME,
                        C.FIRSTNAME,
                        C.DATECHANGED
                    from
                        LOOKUPIDS_CTE LID
                    inner join
                        dbo.CONSTITUENT C on C.ID = LID.ID
                    left join
                        dbo.ADDRESS A on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
                    left join
                        dbo.STATE S on S.ID = A.STATEID
                    )
                    select 
                        ID,
                        NAME,
                        ADDRESSBLOCK,
                        CITY,
                        STATE,
                        POSTCODE,
                        LOOKUPIDTYPE,
                        LOOKUPID,
                        100 / coalesce((select count(distinct ID) from FINAL_CTE), 0) MATCHPERCENTAGE,
                        DATECHANGED
                    from FINAL_CTE  
                    order by
                        KEYNAME asc, FIRSTNAME asc;
                end