UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP

create function dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP
(
    @CLIENTUSERSID int,
    @CONSTITUENTID uniqueidentifier
) 
returns uniqueidentifier 
as
begin
    declare @ID uniqueidentifier = null;

    select top 1 @ID = AB.ID 
    from dbo.ADDRESSBOOKFAF AB(nolock)
    inner join dbo.CONSTITUENT C(nolock) on C.ID = @CONSTITUENTID
        and C.FIRSTNAME = AB.FIRSTNAME
        and C.KEYNAME = AB.LASTNAME
        and C.MIDDLENAME = AB.MIDDLENAME
    where AB.CLIENTUSERSID = @CLIENTUSERSID
        and AB.FIRSTNAME <> ''
        and AB.LASTNAME <> ''
        and AB.MIDDLENAME <> ''
        and AB.CONSTITUENTID is null;

    if @ID is null
    begin
        select top 1 @ID = AB.ID 
        from dbo.ADDRESSBOOKFAF AB(nolock)
        inner join dbo.CONSTITUENT C(nolock) on C.ID = @CONSTITUENTID
            and C.FIRSTNAME = AB.FIRSTNAME
            and C.KEYNAME = AB.LASTNAME
        inner join dbo.EMAILADDRESS E(nolock) on E.CONSTITUENTID = C.ID and E.ISPRIMARY = 1
            and E.EMAILADDRESS = AB.EMAILADDRESS
        where AB.CLIENTUSERSID = @CLIENTUSERSID
            and AB.FIRSTNAME <> ''
            and AB.LASTNAME <> ''
            and AB.EMAILADDRESS <> ''
            and AB.CONSTITUENTID is null;
    end

    if @ID is null
    begin
        select top 1 @ID = AB.ID 
        from dbo.ADDRESSBOOKFAF AB(nolock)
        inner join dbo.CONSTITUENT C(nolock) on C.ID = @CONSTITUENTID
            and C.FIRSTNAME = AB.FIRSTNAME
            and C.KEYNAME = AB.LASTNAME
        where AB.CLIENTUSERSID = @CLIENTUSERSID
            and AB.FIRSTNAME <> ''
            and AB.LASTNAME <> ''
            and AB.CONSTITUENTID is null;
    end

    return @ID;
end