PDA

View Full Version : Solved: Conditional ORDER BY in MS SQL



jtrowbridge
12-07-2007, 08:33 AM
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. :bug:


Thanks in advance!

XLGibbs
12-08-2007, 08:28 AM
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

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




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



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)