UFN_SALESORDER_CONTACTRECORDS

CREATE function dbo.UFN_SALESORDER_CONTACTRECORDS(@ID uniqueidentifier)
returns table
as return (
    -- Getting the contact relationship records with a subselect in order to make sure

    -- we only hit the tables being selected by the caller of the function.  Execution plans

    -- about the same with subselects as they do for left joins when selecting all columns.


    -- Note: This select top 1 isn't necessary for the function, but with it, query analyzer

    -- can use this information to know that their is a one-to-optionally one relationship and ignore

    -- an unused outer apply

    select top 1
        case
            when SALESORDER.CONTACTRELATIONSHIPID is null then SALESORDER.ADDRESSID
            else (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.RELATIONSHIPID = SALESORDER.CONTACTRELATIONSHIPID)
        end as ADDRESSID,
        case
            when SALESORDER.CONTACTRELATIONSHIPID is null then SALESORDER.PHONEID
            else (select top 1 PHONE.ID from dbo.PHONE where PHONE.RELATIONSHIPID = SALESORDER.CONTACTRELATIONSHIPID)
        end as PHONEID,
        case
            when SALESORDER.CONTACTRELATIONSHIPID is null then SALESORDER.EMAILADDRESSID
            else (select top 1 EMAILADDRESS.ID from dbo.EMAILADDRESS where EMAILADDRESS.RELATIONSHIPID = SALESORDER.CONTACTRELATIONSHIPID)
        end as EMAILADDRESSID
    from
        dbo.SALESORDER
    where
        SALESORDER.ID = @ID
)