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

Thanks for the reply Beth. Can you give details on using the presentation layer to do this? We will be using a strongly typed DataSet in an ASP.Net GridView. — Emad-ud-deen, Dec 12 '12 at 18:17
I'm assuming you're presenting the output somewhere, can you do it there? what tool are you using for your front end? — Beth, Dec 12 '12 at 18:19
For a small GridView with only 2 columns we created 2 separate queries in the DataSet designer and call it from the code-behind file but this will be difficult to maintain with we do a bigger DataView with 10 columns in it. — Emad-ud-deen, Dec 12 '12 at 18:22
If VS would allow us to use ORDER BY @OrderByColumn, that will eliminate a lot of extra coding. — Emad-ud-deen, Dec 12 '12 at 18:24
you have to pass the @orderByColumn as a parameter to your grid / dataview — Beth, Dec 12 '12 at 18:25
The problem we are having is that VS will not allow us to use ORDER BY @OrderByColumn because it will display the error message when we click the "Next" button of the wizard. If it lets us past that point then we can make the coding for handling the parameter to pass to it. — Emad-ud-deen, Dec 12 '12 at 18:28

score 61 · Accepted Answer · answered Dec 12 '12 at 18:29

61

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.

answered Dec 12 '12 at 18:29

Branko Dimitrijevic

50,809
10
93
167

Thanks for the help. Looks like VS doesn't like parameters for column names. At least your way will cut down of some coding. :-) – Emad-ud-deen Dec 12 '12 at 18:33
That's what we did for the 2 column DataView since it was only 2 columns. The 20 column one would require quite a lot of coding though. – Emad-ud-deen Dec 12 '12 at 18:36
The DataSet designer is telling me to "Declare the scalar variable @OrderByColumn". Can you tell me how to do it in the designer? – Emad-ud-deen Dec 12 '12 at 18:49
@Emad-ud-deen Sorry, I'm not familiar with that. In SQL Server Management Studio, you'd simply: `DECLARE @OrderByColumn int = 1;` and in ADO.NET code you'd use bound parameters ([SqlParameter](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx) class). – Branko Dimitrijevic Dec 12 '12 at 18:53
Maybe it's best to do the query as a stored procedure. Since I'm new to stored procedures, can you show a simple stored procedure that would implement @OrderByColumn ? – Emad-ud-deen Dec 12 '12 at 19:02
I found out how to do stored procedures. It works nicely now. Thanks again for the help with the Case statement. – Emad-ud-deen Dec 13 '12 at 14:23
This is so usefull, took me 2 hours to find it, thanks a lot – Antoine Pelletier Apr 20 '16 at 20:24
I suspect most never put even close to the amount of effort as this to close up this serious SQL Injection hole. Binding for params but not for order By.. – Dan Chase Jun 01 '18 at 17:50
1

To avoid the optimizer to mess this up, you can add `OPTION (RECOMPILE)` at the end of your query – Carlo Bos Feb 11 '19 at 17:34
@CarloBos Good suggestion, though that has its own performance implications (on the order of up to several hundred milliseconds, in my experience). So not advisable for smaller tables, but definitely worth it for larger ones. – Branko Dimitrijevic Feb 12 '19 at 06:35

Mad hatter · Answer 2 · 2023-04-14T12:30:06.623

The accepted answer does not work if columns are of different types. You get a "Conversion failed when converting date and/or time from character string." for instance.

I've used a double-CASE (or triple or whatever) solution, each CASE accepting the same type of data, filling with NULL values the others.

I know, it's ugly, and certainly not performant ^^

ORDER BY
    CASE -- process CASE for VARCHAR
        WHEN @OrderByColumn = 1 THEN Forename
        WHEN @OrderByColumn = 2 THEN Surname
        ELSE NULL
    END,
    CASE -- process CASE for DATETIME
        WHEN @OrderByColumn= 3 THEN BirthDate
        ELSE NULL 
    END

"Order By" using a parameter for the column name

2 Answers2

Linked

Related