"Order By" using a parameter for the column name

You should be able to do something like this:

SELECT *FROM    TableNameWHERE    (Forename LIKE '%' + @SearchValue + '%') OR    (Surname LIKE '%' + @SearchValue + '%') OR    (@SearchValue = 'ALL')ORDER BY     CASE @OrderByColumn    WHEN 1 THEN Forename    WHEN 2 THEN Surname    END;

• Assign 1 to @OrderByColumn to sort on Forename.
• Assign 2 to sort on Surname.
• Etc... you can expand this scheme to arbitrary number of columns.

Be careful about performance though. These kinds of constructs may interfere with query optimizer's ability to find an optimal execution plan. For example, even if Forename is covered by index, query may still require the full sort instead of just traversing the index in order.

If that is the case, and you can't live with the performance implications, it may be necessary to have a separate version of the query for each possible sort order, complicating things considerably client-side.

I know that I'm coming in to this thread way late, but I just want to post this in case anyone else has a similar problem.

The issue seems to occur when you try to perform an ORDER BY directly on the parameter, because SQL Server expects you to provide a number (1 for the first field, 2 for the second, and so on...), or a column name presented either as an identifier (MyField or "MyField") or a string ('MyField').

For example:

DECLARE @ORDERBY AS NVARCHAR(20);SELECT @ORDERBY = :Param1 --(Supposing that the user enters 'MyField');SELECT TOP 1 *FROM MyTableORDER BY @ORDERBY DESC;

You get the following error:

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. (SQLSTATE=42000) (1008) (Severity=16)

If you write out the query manually in any one of the described ways (using an identifier or a string), there is no error.

SELECT TOP 1 *FROM MyTableORDER BY MyField DESC;SELECT TOP 1 *FROM MyTableORDER BY "MyField" DESC;SELECT TOP 1 *FROM MyTableORDER BY 'MyField' DESC;

So if you perform a CAST() on that same parameter, its value is converted to a string, and the query executes successfully:

DECLARE @ORDERBY AS NVARCHAR(20);SELECT @ORDERBY = :Param1 --(Supposing that the user enters the text 'MyField');SELECT TOP 1 *FROM MyTableORDER BY CAST(@ORDERBY AS NVARCHAR(20)) DESC;

In this instance, (again, supposing that the user wrote the string 'MyField' as the value of :Param1), the actual query being executed is:

SELECT TOP 1 *FROM MyTableORDER BY 'MyField' DESC;

This query executes successfully, with no errors and no apparent, significant impact on performance, without the need to enumerate all of the possible user inputs into a CASE statement that could, potentially, stretch out to hundreds of possible values.

I have used this solution many times in Microsoft SQL Server, from 2005 up to 2016, with no problems whatsoever.

Hopefully this can still be helpful to someone.