Dynamic ORDER BY clause in SQL Server 2008 R2

Question

We have a stored procedure where we want to dynamically let the user choose which column to sort the result by, and in which order (ascending/descending). Currently, we have the following ORDER BY clause, but I'm sure it can be optimized. How?

ORDER BY
    CASE WHEN @OrderBy = 'salvnummer' AND @OrderByDirection = 'DESC' THEN salvnummer END DESC, 
    CASE WHEN @OrderBy = 'salvnummer' AND @OrderByDirection = 'ASC' THEN salvnummer END, --ASC
    CASE WHEN @OrderBy = 'entreprenaddel' AND @OrderByDirection = 'DESC' THEN entreprenaddel END DESC, 
    CASE WHEN @OrderBy = 'entreprenaddel' AND @OrderByDirection = 'ASC' THEN entreprenaddel END, --ASC
    CASE WHEN @OrderBy = 'sektion' AND @OrderByDirection = 'DESC' THEN sektion END DESC, 
    CASE WHEN @OrderBy = 'sektion' AND @OrderByDirection = 'ASC' THEN sektion END, --ASC
    CASE WHEN @OrderBy = 'tid' AND @OrderByDirection = 'DESC' THEN tid END DESC,
    CASE WHEN @OrderBy = 'tid' AND @OrderByDirection = 'ASC' THEN tid END --ASC

Both input parameters are currently varchars.

Answer 1

Your query won't be able to use an index to perform the sorting. If you want to use an index then you will have to build the query using dynamic SQL so that the query that ends up running only contains the ORDER BY clause for the relevant column. This article explains how you could do that. THe SQL could be created like this:

'ORDER BY ' + @OrderBy + ' ' + @OrderByDirection

Be aware that using dynamic SQL increases the risk of SQL injection so make sure to validate your inputs.

Answer 2

You could of course use dynamic SQL and create the ORDER BY according to the variables. I wouldn't be to sure however, that this would necessarely lead to a more efficient query. The optimizer may very well simplify your current solution as soon as the paramter values are available.