Dynamic Sql Where And Order By Clauses In Stored Procedure

Hi,

I hope some one can help me.

I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done?


ALTER PROCEDURE [dbo].[sp_aggregate]
    -- Add the parameters for the stored procedure here
@finfileid int,    
@phaseid int,
@supplierid int,
@measurementid int,
@roleid int,
@groupby int,
@orderby int,
@where int
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT     MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId,
                      SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail
                       / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId)
                      AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId)
                      AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName,
                      MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName,
                      MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId,
                      MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid
FROM         ProjectFinFileItems INNER JOIN
                      ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN
                      FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN
                      Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN
                      Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN
                      ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN
                      Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId



/*dynamic where clause needs to go here */



/*dynamic group by clause */

GROUP BY
CASE when @groupby=1 then ProjectFinFileItems.projphaseid  --phaseid
when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid
when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment
when @groupby=4 then ProjectFinFileMonthItems.roleid --role


else
ProjectFinFileMonthItems.ProjFinFileMonthItemsId
END

/*dynamic order clause needs to go here */


END

cheers

Mark :)