USP_SIMPLEDATALIST_PROSPECTMANAGER_FULL

CREATE procedure dbo.USP_SIMPLEDATALIST_PROSPECTMANAGER_FULL as
select distinct
    case 
        when C.ISCONSTITUENT = 1 and C.ISGROUP = 0 and C.ISORGANIZATION = 0 then
            case 
                when (NULLIF(C.FIRSTNAME, '') is null) and (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME
                when (NULLIF(C.FIRSTNAME, '') is null) and not (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME + ', ' + C.MIDDLENAME
                when not (NULLIF(C.FIRSTNAME, '') is null) and (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME + ', ' + C.FIRSTNAME
                else C.KEYNAME + ', ' + C.FIRSTNAME + ' ' + C.MIDDLENAME
            end
        else
            C.NAME
        end as VALUE
    ,case 
        when C.ISCONSTITUENT = 1 and C.ISGROUP = 0 and C.ISORGANIZATION = 0 then
            case 
                when (NULLIF(C.FIRSTNAME, '') is null) and (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME
                when (NULLIF(C.FIRSTNAME, '') is null) and not (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME + ', ' + C.MIDDLENAME
                when not (NULLIF(C.FIRSTNAME, '') is null) and (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME + ', ' + C.FIRSTNAME
                else C.KEYNAME + ', ' + C.FIRSTNAME + ' ' + C.MIDDLENAME
            end
        else
            C.NAME
        end as LABEL
from dbo.PROSPECT P
inner join dbo.CONSTITUENT C on P.PROSPECTMANAGERFUNDRAISERID = C.ID
order by 
    case 
        when C.ISCONSTITUENT = 1 and C.ISGROUP = 0 and C.ISORGANIZATION = 0 then
            case 
                when (NULLIF(C.FIRSTNAME, '') is null) and (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME
                when (NULLIF(C.FIRSTNAME, '') is null) and not (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME + ', ' + C.MIDDLENAME
                when not (NULLIF(C.FIRSTNAME, '') is null) and (NULLIF(C.MIDDLENAME, '') is null) then C.KEYNAME + ', ' + C.FIRSTNAME
                else C.KEYNAME + ', ' + C.FIRSTNAME + ' ' + C.MIDDLENAME
            end
        else
            C.NAME
    end