UFN_CONSTITUENTMERGEPROCESS_DETERMINEMOSTCOMPLETENAME

create function dbo.UFN_CONSTITUENTMERGEPROCESS_DETERMINEMOSTCOMPLETENAME
(
    @SOURCEID uniqueidentifier,
    @TARGETID uniqueidentifier
)
returns uniqueidentifier
with execute as caller
as begin
    declare @MOSTCOMPLETEID uniqueidentifier;
    declare @NUMFIELDS_SOURCE integer;
    declare @NUMFIELDS_TARGET integer;
    declare @NAME_TARGET nvarchar(154);
    declare @NAME_SOURCE nvarchar(154);

    select 
        @NAME_SOURCE = NAME,
        @NUMFIELDS_SOURCE = 
            case when LEN(KEYNAME) > 0 then 1 else 0 end
            + case when LEN(KEYNAMEPREFIX) > 0 then 1 else 0 end
            + case when LEN(FIRSTNAME) > 0 then 1 else 0 end
            + case when LEN(MIDDLENAME) > 0 then 1 else 0 end
            + case when LEN(MAIDENNAME) > 0 then 1 else 0 end
            + case when LEN(NICKNAME) > 0 then 1 else 0 end
            + case when TITLECODEID is null then 0 else 1 end
            + case when TITLE2CODEID is null then 0 else 1 end
            + case when SUFFIXCODEID is null then 0 else 1 end
            + case when SUFFIX2CODEID is null then 0 else 1 end
    from dbo.CONSTITUENT
    where ID = @SOURCEID;    

    select
        @NAME_TARGET = NAME,
        @NUMFIELDS_TARGET = 
            case when LEN(KEYNAME) > 0 then 1 else 0 end
            + case when LEN(KEYNAMEPREFIX) > 0 then 1 else 0 end
            + case when LEN(FIRSTNAME) > 0 then 1 else 0 end
            + case when LEN(MIDDLENAME) > 0 then 1 else 0 end
            + case when LEN(MAIDENNAME) > 0 then 1 else 0 end
            + case when LEN(NICKNAME) > 0 then 1 else 0 end
            + case when TITLECODEID is null then 0 else 1 end
            + case when TITLE2CODEID is null then 0 else 1 end
            + case when SUFFIXCODEID is null then 0 else 1 end
            + case when SUFFIX2CODEID is null then 0 else 1 end
    from dbo.CONSTITUENT
    where ID = @TARGETID;

    if @NUMFIELDS_TARGET > @NUMFIELDS_SOURCE
        set @MOSTCOMPLETEID = @TARGETID;

    else if @NUMFIELDS_SOURCE > @NUMFIELDS_TARGET
        set @MOSTCOMPLETEID = @SOURCEID;

    else
        -- Equal number of name fields populated...go with the longest

        begin
            if LEN(@NAME_TARGET) > LEN(@NAME_SOURCE)
                set @MOSTCOMPLETEID = @TARGETID;
            else
                set @MOSTCOMPLETEID = @SOURCEID;
        end

    return @MOSTCOMPLETEID;
end