“Order By” using a parameter for the column name

by

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.

More Related Contents:

  1. Parameterize an SQL IN clause
  2. How to set table name in dynamic SQL query?
  3. When no ‘Order by’ is specified, what order does a query choose for your record set?
  4. Pass table as parameter into sql server UDF
  5. How to update and order by using ms sql
  6. SQL Server UNION – What is the default ORDER BY Behaviour
  7. Can we pass parameters to a view in SQL?
  8. how does SELECT TOP works when no order by is specified?
  9. How to get results in a custom order? [closed]
  10. Dynamic order direction
  11. Parameterise table name in .NET/SQL?
  12. Call stored procedure with table-valued parameter from java
  13. Case statement for multiple columns
  14. SQL where clause odd behavior [closed]
  15. SQL Server Exec issue [closed]
  16. Count(*) vs Count(1) – SQL Server
  17. Selecting data from two different servers in SQL Server
  18. Is a view faster than a simple query?
  19. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  20. Should I design a table with a primary key of varchar or int?
  21. How do you import a large MS SQL .sql file?
  22. How do I make a composite key with SQL Server Management Studio?
  23. Difference between Subquery and Correlated Subquery
  24. Does Sql JOIN order affect performance?
  25. Best equivalent for IsInteger in SQL Server
  26. Syntax of for-loop in SQL Server
  27. Using tuples in SQL “IN” clause
  28. Why does ISNUMERIC(‘.’) return 1?
  29. How to execute a stored procedure inside a select query
  30. SQL Inner Join On Null Values

Leave a Comment