SELECT DISTINCT
  CS.FinNumber,
  CB.BranchName AS ClientName,
  TRIM(CONCAT(TRIM(CONCAT(TRIM(CC.Title), ' ', TRIM(CC.FirstName))), ' ', TRIM(CC.Surname))) AS ContactName,
  CC.Position,
  CC.Email,
  CONCAT(CC.TelephoneAreaCode, CC.TelephoneNo) AS TelephoneNo
FROM
  taClientContacts AS CC
    LEFT JOIN taClientBranches AS CB ON ((CB.ClientBranchID = CC.TypeID) AND (CC.ContactType = 'ClientBranch'))
    LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientID = CB.ClientID)
WHERE
  1
  @pClientID
  @pBranchID
  @pProductID
  @pClientStatus
  @pBranchStatus
  @pStatus
ORDER BY
  CB.BranchName,
  CC.Surname