Hi,I am writing a stored procedure that accepts one value from an ADO Command object. This value determines what to order my query by.Here is a snippet of the query:CREATE PROCEDURE dbo.chg_GetCompleted@intOfficeID int,@intClientID int,@intLocationID int,@dteDateFrom smalldatetime,@dteDateTo smalldatetime,@strSort varchar(20)ASIF @strSort = 'DateRec' BEGIN SELECT C.c_ControlID, C.c_DateFrom, C.c_DateTo, C.c_DateReceived, C.c_DateCompleted, C.c_ChgEntID, L.LocName FROM Charges AS C INNER JOIN Locations AS L ON L.Loc_ID = C.c_LocationID INNER JOIN Clients AS CL ON CL.Clt_ID = L.ClientID INNER JOIN Office AS O ON O.Office_ID = CL.OfficeID WHERE C.c_DateCompleted IS NOT Null AND C.c_DateCompleted BETWEEN @dteDateFrom AND @dteDateTo AND C.c_Deleted = 0 AND O.Office_ID = @intOfficeID AND CL.Clt_ID = @intClientID AND L.Loc_ID = @intLocationID ORDER BY C.c_DateReceived DESC END
The @strSort variable can be one of three values. Currently in each if/else section, I am writing the same query with the only difference being the ORDER BY field. Instead of writing the same query 3 times I would like to do something like this:CREATE PROCEDURE dbo.chg_GetCompleted@intOfficeID int,@intClientID int,@intLocationID int,@dteDateFrom smalldatetime,@dteDateTo smalldatetime,@strSort varchar(20)AS SELECT C.c_ControlID, C.c_DateFrom, C.c_DateTo, C.c_DateReceived, C.c_DateCompleted, C.c_ChgEntID, L.LocName FROM Charges AS C INNER JOIN Locations AS L ON L.Loc_ID = C.c_LocationID INNER JOIN Clients AS CL ON CL.Clt_ID = L.ClientID INNER JOIN Office AS O ON O.Office_ID = CL.OfficeID WHERE C.c_DateCompleted IS NOT Null AND C.c_DateCompleted BETWEEN @dteDateFrom AND @dteDateTo AND C.c_Deleted = 0 AND O.Office_ID = @intOfficeID AND CL.Clt_ID = @intClientID AND L.Loc_ID = @intLocationID IF @strSort = 'DateRec' BEGIN ORDER BY C.c_DateReceived DESC END ELSE IF (blah blah.....)
Is there a way I could do this?