Dynamic Order By for SQL query
-
I've been pondering for a while now whether there's a way using MSSQL to dynamically determine what column should be ordered by in a select (EG: "SELECT * FROM table1 ORDER BY [b]column asc[/b]", dynamically editing the bold part). In a perfect world I'd love to make it parameter-driven but I'm pretty sure there's no simple way to achieve that...
I've google'd it a bit but it hasn't really been much help. The only ways I've come up with to achieve this are:
1. Defining all the possible queries in advance (Not so good because when you have a lot of columns you can sort on becomes painful to create and maintain)
2. Building the SQL query on the fly (Which I'd prefer not to do - while I could probably safely escape it I still think this is sort of poor form and typically all our queries are in XSD's and this would force it to be done differently to all other queries in the applications)
3. Building the query and using Exec in a stored procedure to run it (I'm leaning towards this, it removes a few of the disadvantages of option 2 like not using XSD's but I've got it in my head for obvious reasons to avoid Exec wherever possible).
4. Sorting it programatically (Which is nice and tidy, but unfortunately doesn't scale well when you start dealing with larger tables and are trying to return only a few hundred rows of data).Curious what people think is the "lesser evil", or if anyone has an option 5 I haven't considered yet...
-
Option 5: Use a parameter to specify the sort type and use it in a case statement in the order by clause.
Really only good for simple sorting definitions. Anything complicated, I'd probably go with dynamic SQL and use sp_executesql.
CREATE PROCEDURE MyProc @SortType int AS BEGIN
SELECT
MyTable.FieldA,
MyTable.FieldB
FROM
MyTable
ORDER BY
CASE
WHEN @SortType = 1 THEN FieldA
WHEN @SortType = 2 THEN FieldB
ELSE FieldA
ENDEND
-
Yeah, I considered that option, but I need to be able to change the asc/desc too. I guess that'd reduce it to 2 queries though, which is something, but it'd be a real mess with the number of fields it's on (Plus it'd be painful to add sorting by a new field which is basically guaranteed to come up a few times in the life of the app).
I didn't know about sp_executesql though, only execute, so that's a hell of a lot tidier. If I wrapped the sp_executesql call in a stored procedure of my own so that all it can change is the sort expression I think that could actually be a relatively neat way of dealing with this... Thanks =)
-
It may not be useful, but my first thought is, if you have to do something this awkward, is it really really needed? Perhaps there's a compromise that will keep the users perfectly happy and give you a lesser problem. For example, if you only had, say, 4 possible sort columns, the problem becomes trivial. Go and bully the BAs? Just saying...
-
@vr602 said:
It may not be useful, but my first thought is, if you have to do something this awkward, is it really really needed? Perhaps there's a compromise that will keep the users perfectly happy and give you a lesser problem. For example, if you only had, say, 4 possible sort columns, the problem becomes trivial. Go and bully the BAs? Just saying...
Yeah, tried that first. No dice. I'm not convinced all the sorting options will ever be used after UAT, I reckon only 2 of the fields need a sort, but I am convinced short of a miracle the client won't budge on the requirement so it doesn't matter how useless it is ;)
-
@fyjham said:
Yeah, tried that first. No dice. I'm not convinced all the sorting options will ever be used after UAT, I reckon only 2 of the fields need a sort, but I am convinced short of a miracle the client won't budge on the requirement so it doesn't matter how useless it is ;)
Sadly this sounds all too familiar. Depending on what you're doing with the data is a sorting option at the UI level, ie, clickable column headers, viable or is this for a report or something?
-
@JesusChrist said:
Sadly this sounds all too familiar. Depending on what you're doing with the data is a sorting option at the UI level, ie, clickable column headers, viable or is this for a report or something?
That was my original approach, and is typically the way I do it just to save time despite it not being optimal for performance. But in this case the dataset is simply too large to actually load it all to the UI then sort it. The sheer amount of data transferred from the database server slows it to the point it takes like 5-10 seconds to sort the page which isn't really acceptable (Especially when the size of the database is likely to be at least 10 times larger by the end of the year ;))
-
@fyjham said:
The sheer amount of data transferred from the database server slows it to the point it takes like 5-10 seconds to sort the page which isn't really acceptable
fair enough, just a thought but could you select into a temp table then build the sql to select from that dynamically build a string like
'SELECT * FROM #temptable order by ' + @sortcolumn + ' ' + @sortdirection
for added security you could check the table definition to make sure the passed in column actually exists in the table. Not sure if this solution would help but it certainly reduces your need for escaping. probably has a bit of overhead associated with the temp table too.
-
What would be gained from doing it into the temp table rather than straight to the original table? Seems like overhead for no gain since it's still using a concatenated query going into a sp_executesql? Plus it loses the benefits of indexes that exist on the main table.
I guess it cuts the main query off from the sp_executesql but I can't see why that's a benefit over appending a sort to the main query then sp_executesql'ing it when sp_executesql allows everything else to stay parameterized. I wasn't aware it could do that till lpope's comment made me look at it - I've only really seen non-parameter dynamic SQL queries - which basically gets around my biggest problem with that approach so I'm thinking that's a solution.
I was thinking the same thing on the column names though, hadn't decided whether to find the available names or to just limit the names that can be put in to alphanumeric with no spaces (Hard to SQL inject with onlythat to work with. I prefer a white list to escaping wherever possible, much harder to miss something in a whitelist than blacklist ;)).
Thanks for the help, especially sp_executeSQL. With that to help I think I can safely avoid having to post my own solution on the sidebar :P
-
@fyjham said:
Aaaaah. I thought so. This is how I would play it:@vr602 said:
It may not be useful, but my first thought is, if you have to do something this awkward, is it really really needed? Perhaps there's a compromise that will keep the users perfectly happy and give you a lesser problem. For example, if you only had, say, 4 possible sort columns, the problem becomes trivial. Go and bully the BAs? Just saying...
Yeah, tried that first. No dice. I'm not convinced all the sorting options will ever be used after UAT, I reckon only 2 of the fields need a sort, but I am convinced short of a miracle the client won't budge on the requirement so it doesn't matter how useless it is ;)
1. Implement sort on 2 columns. Keep fancy multi-column sort up your sleeve in case;
2. Tell users that the sort on other columns has been de-prioritised to end of list in favour of more important things, but that it will def be in phase 2;
3. UAT and implementation successful;
4. Multi-sort forgotten;
5. Support and performance are a joy, not a bastard.
Done.
-
Why does all SQLhave to look pretty? Do what lpope suggested, but add another parameter for sort direction and an IF statement
-
@Salami said:
Why does all SQLhave to look pretty? Do what lpope suggested, but add another parameter for sort direction and an IF statement
Pretty I don't care about, maintainable I do. With duplicate code (Which let's face it, the second something is in 2 spots 1 gets updated and not the other at some point down the line), stored procedure needing to be updated for any new sortable column that comes along (Which is likely to occur) and the world's largest SQL case statement (For the number of columns that need to be sorted by) I just wouldn't think it was a highly maintainable enough solution.
I ended up going with an sp_executesql approach, just added some basic checking to prevent SQL injection (Sort direction must be asc or desc and column name passed in doesn't contain ] so I can safely just go [ColumnName] - if anyone is stupid enough to put a ] in a column name I'll rename their column instead of change the query - and also wrapped it at the DAL level in a check against the available columns before it gets passed to the stored procedure mostly just to get more sane errors if it's called wrong).I hated the idea of executing the SQL from a string, but since sp_executesql actually allows you to still make it a parameterized query I'm not as annoyed by it. It should still get compiled and executed with a shared execution plan (Well, one for each sort column/direction, but it's better than 1 for every SQL command).
Oh and vr602, the problem is that the app was already released once with the feature, and I was just doing the sorting in the display layer, and then the amount of data expected exponentially changed so they've come back for it to be optimized for the new amount of data. I've tried asking about removing the feature but it went nowhere, the "sweep it under the rug" tactic has been attempted and unfortunately I just don't have a big enough rug laying around :P
-
You have my sympathy. Good luck with the dynamic thing.
-
If you want to further reduce the possibility of SQL Injection, just use the EXECUTE AS clause of the proc definition. That way you can force all statements in the context of the procedure to be run with a user that only has SELECT rights to the objects the dynamic statement references.
-
Guys, lpope has it right. You don't need dynamic SQL, just ORDER BY CASE expressions that test your parameters. Look at the code below. Looks clunky but runs sweet. Lets you sort by up to three columns, and could easily be extended to six or twelve. Handles both ascending and descending sorts by a simple intuitive convention. Took 5 minutes to code, because after you get the first set done, everything else is cut, paste, and replace. One execution plan, no SQL injection risk. ;-)
Painful to change? Come on.... what could be simpler? All you're doing is adding a few more WHEN lines. Its a small price to pay for the performance and the security.
-- values for parameters: 1 = col [A], 2 = col[B], 3 = col[C]
-- negative values mean descending orderdeclare @parm1 int, @parm2 int, @parm3 int
-- sort on column B, column C descending, and finally column A (play around with these settings.)
select @parm1 = 2, @parm2 = -3, @parm3 = 1
;with sample (A,B,C) as
(select 1,2,3 union all
select 9,2,5 union all
select 3,4,5 union all
select 2,3,1 union all
select 2,2,1
)
select A,B,C from sample
order by
case when @parm1 = 1 then A
when @parm1 = 2 then B
when @parm2 = 3 then C
else null end ASC,
case when @parm1 = -1 then A
when @parm1 = -2 then B
when @parm2 = -3 then C
else null end DESC,
case when @parm2 = 1 then A
when @parm2 = 2 then B
when @parm2 = 3 then C
else null end ASC,
case when @parm2 = -1 then A
when @parm2 = -2 then B
when @parm2 = -3 then C
else null end DESC,
case when @parm3 = 1 then A
when @parm3 = 2 then B
when @parm3 = 3 then C
else null end ASC,
case when @parm3 = -1 then A
when @parm3 = -2 then B
when @parm3 = -3 then C
else null end DESC
-
And, to my chagrin, there is a typo. The first two case statements should be testing "when @parm1 = 3" not "when @parm2 = 3".
I didn't see it in time to edit it. So it goes.