Is it possible to order by any column given a stored procedure parameter in SQL Server?

Question

I was looking into sorting tables by a column designated given some input, and from what I've found, there is no easy way to do this. The best I've found is a switch statement:

SELECT Column1, Column2, Column3, Column4
FROM Table
ORDER BY CASE WHEN @OrderBY = 'Column1' THEN Column1
              WHEN @OrderBY = 'Column2' THEN Column2
              WHEN @OrderBY = 'Column3' THEN Column3
              WHEN @OrderBY = 'Column4' THEN Column4

Is it possible to do this without having a CASE statement like that? If the table gets bigger and more columns need to be sorted by, this could become messy.

The only way I've been able to do this is by just concatenating a big SQL string, which sort of defeats the advantages of Stored Procedures, and makes the SQL hard to write and maintain.

Answer 1

I generally convert the stored procedure to a function that returns a table ( so you can select FROM it ... and add the dynamic order by columns to it in the application code:

Select
    *
From
   myTableFUnction()
Order by
   1, 2, 3, 6  <-- defined by application code in the SQL for the query

Ron

Answer 2

You have two choices:

1. As you have implemented above

2. Or generate dynamic sql and execute using sp_executesql

Answer 3

The RANK feature of SQL Server and Oracle can improve performance and makes the code a little cleaner:

SQL:

DECLARE @column varchar(10)

SET @column = 'D'

SELECT *
FROM Collection.Account AS A
ORDER BY 
      CASE 
            WHEN @column = 'A' THEN (RANK() OVER(ORDER BY A.Code ASC))
            WHEN @column = 'D' THEN (RANK() OVER(ORDER BY A.Code DESC))
      END

Answer 4

In this case, unless you have an extremely large dataset and you need to leverage the power of the database server (thin client, weak client machine, etc), it is best to sort within the client.

Answer 5

You already write the correct syntax:

SELECT Column1, Column2, Column3 
FROM SOME_TABLE
ORDER BY 1,2,3

try it