Can I order any column with a stored procedure parameter in SQL Server?

Question

I studied sorting tables by the column specified by input, and from what I found there is no easy way to do this. The best I have found is the 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 such a CASE statement? If the table gets larger and more columns need to be sorted, this can become messy.

The only way I was able to do this was to simply concatenate a large SQL string, which spoils the benefits of stored procedures and makes SQL difficult to write and maintain.

Answer 1

You have two options:

• As you have already completed above

• Or create dynamic sql and execute using sp_executesql

Answer 2

I usually convert the stored procedure into a function that returns a table (so that you can select FROM it ... and add dynamic column order to the application code:

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

Ron

Answer 3

The RANK function of SQL Server and Oracle can improve performance and make 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

You are already writing the correct syntax:

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

try

Answer 5

In this case, if you do not have an extremely large data set, and you need to use the capabilities of the database server (thin client, weak client computer, etc.), it is best to sort inside the client.