SELECT
  CB.ClientID,
  CB.ClientBranchID,
  CB.BranchName,
  BR.BranchName AS LabourNetBranch,
  IF(CB.BranchStatus = 1, 'ACTIVE', 'CANCELLED') AS BranchStatus,
  IF(CB.IsHeadOffice = 1, 'YES', 'no') AS IsHeadOffice,
  fnClientBranchRetainer(CB.ClientBranchID, NOW()) AS BranchRetainer,
  IF(TRIM(CB.MapReference) = '', '', '<img style="height: 15px;" src="/main/image/globe.png">') AS MapIcon
FROM
  taClientBranches AS CB
    LEFT JOIN taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  (CB.ClientID = @nClientID)
  @pBranchStatus
ORDER BY
  CB.IsHeadOffice DESC,
  CB.BranchStatus DESC,
  CB.BranchName
