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)
AS
IF @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?
T-SQL if/else in an ORDER BY clause
Posted - 08/31/2005 : 12:19:05
