SELECT
  CP.ProjectID,
  CP.ClientID,
  CL.FinNumber,
  CL.GroupName AS ClientName,
  CP.ProjectName,
  CP.ConsultantID,
  CP.RiskLevelID,
  SO2.OptionDescription AS RiskLevel,
  US.FullName,
  CP.ProjectTypeID,
  PR.ProductName,
  SO.OptionDescription AS ProjectType,
  CP.ProjectPhaseID,
  SO1.OptionDescription AS ProjectPhase,
  CP.ProjectDescription AS Details,
  CP.StartDate,
  CP.GoLiveDate,
  CP.EmployeeCount,
  CP.PayRollWeekly,
  CP.PayRollFortnightly,
  CP.PayRollMonthly,
  IF(CP.ProjectStatus = 1, 'ACTIVE', 'CLOSED') AS ProjectStatus
FROM
  taClientProjects  AS CP
LEFT JOIN taClients AS CL ON (CL.ClientID = CP.ClientID)
LEFT JOIN taUsers   AS US ON (US.UserID = CP.ConsultantID)
LEFT JOIN taSystemOption AS SO  ON (CP.ProjectTypeID = SO.OptionID AND SO.OptionType = 'PROJECT-TYPE')
LEFT JOIN taSystemOption AS SO1 ON (CP.ProjectPhaseID = SO1.OptionID AND SO1.OptionType = 'PROJECT-PHASE')
LEFT JOIN taSystemOption AS SO2 ON (CP.RiskLevelID = SO2.OptionID AND SO2.OptionType = 'RISK-LEVEL')
LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
WHERE
  1
  @pClientID
  @pConsultantID
  @pRiskLevelID
  @pProjectTypeID
  @pProjectPhaseID
  @pStartDate
  @pGoLiveDate