CODEWITHSUNDEEP
c#sqlado.net
nos
nos

Reputation: 229342

Parameterize 'order by' in SQL

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)

Upvotes: 6

Views: 3364

Answers (6)

Keyvan AryaeeMoeen
Keyvan AryaeeMoeen

Reputation: 11

Sample:

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, Parametrize sorting(Base on Datex Field) in a SELECTION ....

SELECT Num, Date1 FROM ARYA, (SELECT -1 as e union Select 1 as e) a
WHERE a.e=1  --(1) For ASC sort --(OR a.e=-1) For Desc Sort
ORDER BY DATEDIFF(DAY, '2000-01-01', Arya.Datex)*a.e


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

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

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

Upvotes: 6

JBrooks
JBrooks

Reputation: 10013

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.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180958

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.

Upvotes: 3

gbn
gbn

Reputation: 432742

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.

Upvotes: 0

Eric
Eric

Reputation: 8088

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

Upvotes: 0

Related Questions