Phil Parkin (9/6/2012)
mssqlsrv (9/6/2012)
I think you can not use variable in order by clause.Variables are only allowed when ordering by an expression referencing a column name.
Yeah you can. Just tried this and it works:
declare @index int = 4select top 10 * from information_schema.COLUMNS
order by (case @index when 4 then TABLE_SCHEMA else COLUMN_NAME end)
Phil, it is not what OP is asking for.
In case of index != 4 he wants order by the column index not a name!
You remember that it is possible to do
SELECT Col1, Col2, Col3 FROM Table ORDER BY 2
and results will be sorted by the second column (Col2)...
That what OP really is asking for. So - dynamic SQL
...
DECLARE @sql NVARCHAR(1000)
SET @sql = 'SELECT Col1, Col2, Col3, Col4, Col5, ColN FROM Table ORDER BY '
IF @index = 4
SET @sql += 'ColN'
ELSE
SET @sql += CAST( @index AS VARCHAR)
EXEC sp_executesql @sql
-- or just: EXEC (@sql)