Solved: Conditional ORDER BY in MS SQL

[jtrowbridge]

I'm having a tough time getting one of my queries to conditionaly sort on multiple fields. The compiler doesnt like my commas. Here's generally what I have:

---------------------------------------

DECLARE @Toggle AS BIT
SET TOGGLE = 1

SELECT ColumnA, ColumnB, ColumnC, ColumnX, ColumnY, ColumnZ
FROM someplace
WHERE somestuffistrue

ORDER BY
CASE
WHEN @TOGGLE = 1 THEN ColumnA, ColumnB, ColumnC
ELSE ColumnX, ColumnY, ColumnZ
END


----------------------------------------------------

Msg 102, Level 15, State 1, Line 437
Incorrect syntax near ','.

---------------------------------------

I havent had a problem with this code when I'm only sorting on one field but this multi field thing is starting to drive me insane. Again, the compiler throws an error in regards to the commas between the fields in the order by clause.


Thanks in advance!

[XLGibbs]

quite simply "it doesn't work like that". You have to have two statements, one for condition A, one for condition B and use your toggle to decide which one executes
[vba]
DECLARE @Toggle AS BIT
SET TOGGLE = 1

If Toggle = 1
Begin
SELECT ColumnA, ColumnB, ColumnC, ColumnX, ColumnY, ColumnZ
FROM someplace
WHERE somestuffistrue
Order by ColumnA,ColumnB, ColumnC
End

If Toggle != 1
Begin
SELECT ColumnA, ColumnB, ColumnC, ColumnX, ColumnY, ColumnZ
FROM someplace
WHERE somestuffistrue
Order by ColumnX,ColumnY, ColumnZ
End


[/vba]

You can do it by building the SQL dynamically and executing..

[vba]

DECLARE @Toggle BIT, @SQL varchar(8000)
SET TOGGLE = 1

Set @SQL = ' SELECT ColumnA, ColumnB, ColumnC, ColumnX, ColumnY, ColumnZ
FROM someplace
WHERE somestuffistrue
Order by '

If Toggle = 1
Set @SQL = @SQL + ' ColumnA,ColumnB,COlumnC'

If Toggle != 1
Set @SQL = @SQL + ' ColumnX,ColumnY,ColumnZ'

Exec (@SQL)
[/vba]