SELECT DISTINCT
  CP.ProductID,
  PR.ProductName,
  PR.ProductCategory,
  fnClientProductRetainer(CP.ClientProductID, NOW()) AS Retainer
FROM
  taClientProducts AS CP
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
WHERE
  (CP.ClientID = @nClientID) AND
  (CP.Status = 1)
ORDER BY
  PR.ProductName
