• Not to one up Jay here (sorry joke about another thread). But here is another alternative.

    
    
    SELECT
    COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName,
    A.*
    FROM
    Clients C
    INNER JOIN
    InvoiceHeader I
    ON
    I.ClientID=C.ClientID
    INNER JOIN
    Addresses A
    ON
    A.ClientID=C.ClientID AND
    (CASE WHEN A.AddrTypeID=@BillingAddressID THEN 1 ELSE A.PrimaryAddr END) = 1
    WHERE
    I.InvoiceNr=@InvoiceNr

    The only then that confused me was what

    A.AddrTypeID

    values are and what

    @BillingAddressID

    would be. Might have a better example of the code above if I knew that. But I have used this logic before on conditional matching.