Geek Answers Handbook

Parameterize 'order by' in SQL

What is the correct way to parameterize an order by clause in ADO.NET?

Sometimes it is necessary to order 2 columns each, while the default order is only 1 column, and sometimes you just need to change ASC to DEC. We can assume that it is simple to use string concatenation in such cases (provided that the input data does not come directly from the user, but simply search for more or less hard-coded values ​​in the code)

+4
source share
6 answers

Purpose SQL Injection will tell you that string concatenation is never allowed, because there is always the possibility that another programmer can expand the program and put the SQL query into the outside world.

However, if the values ​​are hard-coded (i.e., as a constant) and will never see the outside world, then yes, it is normal to concatenate it.

+2
source

Try it 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 WHEN it does not match. Therefore, if you set Option2, you will get a constant value for the first two parameters.

Thus, using this, you can easily have some options that allow you to sort by multiple fields, or descending, or whatever you want.

Rob

+4
source

Since the user is not allowed to enter text or possibly access the variable through the URL, I see no negative reason when using string concatenation. If, as the guy below says, the program can be expanded by another user who is not a “conscious introduction”.

0
source

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

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

Some answers here: Dynamic direction of orders . The accepted answer and my answer demonstrate two approaches. Perhaps SQL Server is specific.

0
source

If this is not so much data, I would simply:

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

and then change the last line based on what.

0
source

Example:

 SELECT 1 AS Num, '2015-06-22' AS Datex INTO Arya INSERT INTO Arya SELECT 2, '2015-08-17' UNION SELECT 3, '2015-07-14'

Arya table:

 Num Datex ----------------- 1, 2015-06-22 2, 2015-08-17 3, 2015-07-14

Now, sorting the Parametrize (base on the Datex field) into SELECTION ....

 SELECT Num, Date1 FROM ARYA, (SELECT -1 as e union Select 1 as e) a WHERE ae=1 --(1) For ASC sort --(OR ae=-1) For Desc Sort ORDER BY DATEDIFF(DAY, '2000-01-01', Arya.Datex)*ae Result: ASC Sort IF 1 1, 2015-06-22 3, 2015-07-14 2, 2015-08-17 Result: ASC Sort IF -1 2, 2015-08-17 3, 2015-07-14 1, 2015-06-22
0
source

More articles:


All Articles