"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.