problems with case statement in where clause

[Delerna]

[code=sql]
SELECT *
FROM CALL
ORDER BY @orderBy
[/code]

Order by works with field names. You can't use a variable in the order by like that

You can say
[code=sql]
SELECT *
FROM CALL
ORDER BY case when @orderBy='test' then FieldName1 else FieldName2 end
[/code]


Also from a previous post
[code=sql]
Declare @orderBy char(1)
Set @orderBy = 'A'
SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END
[/code]

You can't reference multiple fields in the case like that
you need a case statement for each field
[code=sql]
Declare @orderBy char(1)
Set @orderBy = 'A'
SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY
CASE WHEN @orderBy = 'A' THEN START_DATE ELSE CALL_ID END,
CASE WHEN @orderBy = 'A' THEN CALL_ID ELSE START_DATE END
[/code]

So if @orderBy='A' it will be ORDER BY START_DATE,CALL _ID
otherwise it will be ORDER BY CALL_ID,START_D ATE



At least, not in SQLServer 2000 you can't