“Order By” using a parameter for the column name

Question

We would like to use a parameter in the "Order By" clause of a query or stored procedure created with the Visual Studio DataSet Designer.

Example:

  FROM TableName
 WHERE (Forename LIKE '%' + @SearchValue + '%') OR
       (Surname LIKE '%' + @SearchValue + '%') OR
       (@SearchValue = 'ALL')
ORDER BY @OrderByColumn

This error is displayed:

Variables are only allowed when ordering by an expression referencing 
a column name.

Answer 1

You should be able to do something like this:

SELECT *
FROM
    TableName
WHERE
    (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.

Answer 2

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 MyTable
ORDER 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 MyTable
ORDER BY MyField DESC
;

SELECT TOP 1 *
FROM MyTable
ORDER BY "MyField" DESC
;

SELECT TOP 1 *
FROM MyTable
ORDER 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 MyTable
ORDER 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 MyTable
ORDER 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.