Parameterize 'order by' in SQL

Question

What's the proper way of parameterizing an order by clause in ADO.NET ?

Sometimes there's a need to order by 2 columns, while the default is ordering on just 1 column, and sometimes you'll just want to change ASC to DEC. Is it considered ok to just use string concatenating in such cases (provided the input doesn't come from the user directly, but just a lookup of more or less hardcoded values in the code)

Answer 1

The SQL Injection purists will tell you that string concatenation is never permissible, because there always exists the possibility that another programmer may extend the program and expose the SQL statement to the outside world.

However, if the values are hardcoded (i.e. as a constant), and will never see the outside world, then yes, it is perfectly OK to concatenate it.

Answer 2

AS long as a user is not allowed input through text or can possibly access the variable through the url, I don't see any negative reason in using string concatenation. Unless like the guy below says, the program may be extended by a different user that isn't so "injection conscious".

Answer 3

There are pure T-SQL solutions that do not use dynamic SQL.

• Pre- SQL 2005, you had to use CASE in the ORDER BY
• After SQL 2005, you can use ROW_NUMBER etc

Some answers here: Dynamic order direction. The accepted answer and my answer demonstrate the 2 approaches. Perhaps SQL Server specific though.

Answer 4

If it is not that much data, I would just have:

DataTable dt = ....
DataView dv = new DataView(dt);
dv.Sort = "LastName DESC, FistName";

and then vary the last line based on whatever.

Answer 5

Try like this:

SELECT ...
ORDER BY 
   CASE WHEN @OrderBy = 'Option1' THEN SomeField END, 
   CASE WHEN @OrderBy = 'Option1' THEN SomeOtherField END DESC, 
   CASE WHEN @OrderBy = 'Option2' THEN Field75 END, 
  ...

The idea is that each CASE statement will evaluate to NULL if the WHEN doesn't match. So if you put Option2, then you get a constant value for the first two options.

So using this, you can easily have some options that let you sort by several fields, or descending, or whatever you want.

Rob