SQL存储过程中的动态排序 [英] Dynamic Sorting within SQL Stored Procedures
问题描述
这是我过去花费了数小时研究的问题.在我看来，这应该是现代 RDBMS 解决方案应解决的问题，但到目前为止，找不到任何能够真正解决我认为在任何具有数据库后端的Web或Windows应用程序中非常普遍的需求的东西.
我说的是动态排序.在我的幻想世界中，它应该像这样简单:
ORDER BY @sortCol1, @sortCol2
这是新手SQL和在整个论坛上遍布整个论坛的存储过程开发人员给出的规范示例.互联网. 为什么这不可能?"他们问.总是会有人最终向他们讲授存储过程的编译特性，一般的执行计划以及无法将参数直接放入ORDER BY子句的各种其他原因.
我知道你们中有些人已经在想什么:那么，让客户进行分类."自然，这会减轻数据库的工作量.但是在我们的案例中，我们的数据库服务器甚至在99％的时间内都不会汗流breaking背，它们甚至还不是多核的，也不是每6个月进行一次其他任何体系结构的无数改进.仅出于这个原因，让我们的数据库处理排序就不会有问题.此外，数据库非常擅长排序.他们为此进行了优化，并且已经花了好几年的时间来实现它，其实现语言非常灵活，直观，简单，最重要的是，任何SQL初学者都知道该怎么做，更重要的是，他们知道如何对其进行编辑，进行更改，进行维护等.当您的数据库需要负担很多费用而您只想简化(并缩短！)开发时间时，这似乎是一个显而易见的选择.
然后是网络问题.我一直在使用JavaScript进行HTML表格的客户端排序，但是它们不可避免地不够灵活，无法满足我的需求，而且，由于我的数据库没有负担过多，并且可以真正对进行排序，我很难证明重新编写或自己拥有JavaScript分选器所需的时间.服务器端排序通常也是如此，尽管它可能已经比JavaScript更受青睐.我不是特别喜欢数据集开销的人，所以起诉我.
但是，这又回到了不可能的地步.或者说，不容易.在以前的系统中，我已经完成了获得动态排序的令人难以置信的破解方式.它不是很漂亮，也不是直观，简单或灵活的，初学者SQL编写器将在几秒钟内丢失.看起来这已经不仅仅是一种解决方案"，而是一种复杂性".
以下示例无意于公开任何种类的最佳实践或良好的编码风格，也不表示我作为T-SQL程序员的能力.它们就是它们的样子，我完全承认它们是令人困惑的，不良的形式，而且只是简单的破解.
我们将一个整数值作为参数传递给存储过程(我们称该参数为"sort")，然后从中确定一堆其他变量.例如...假设sort为1(或默认值):
DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)
SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';
IF @sort = 1 -- Default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'asc';
SET @sortCol2 = @col2;
SET @dir2 = 'asc';
END
ELSE IF @sort = 2 -- Reversed order default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'desc';
SET @sortCol2 = @col2;
SET @dir2 = 'desc';
END
您已经可以看到，如果我声明更多的@colX变量来定义其他列，我真的可以根据"sort"的值对这些列进行排序...使用它，它通常最终看起来就像以下令人难以置信的混乱子句:
ORDER BY
CASE @dir1
WHEN 'desc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir1
WHEN 'asc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END,
CASE @dir2
WHEN 'desc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir2
WHEN 'asc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END
显然，这是一个精简的示例.真正的东西，因为我们通常有四列或五列支持排序，每列除此以外还可能有第二列，甚至还有第三列可以进行排序(例如，日期递减，然后按名称升序进行第二排序)，并且每一个都支持双向定向排序可有效地将案件数量加倍.是的...真的很快就长毛了.
这个想法是，人们可以轻松地"改变排序条件，以使caridid在storagedatetime之前被排序...但是，至少在这个简单示例中，伪柔韧性才真正到此结束.本质上，每个未通过测试的案例(因为这次我们的sort方法都不适用)会呈现NULL值.因此，您最终得到了一个功能如下的子句:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah
您明白了.之所以起作用，是因为SQL Server有效地忽略了by子句中的空值.这很难维护，因为任何具有SQL基本知识的人都可以看到.如果我失去了你们中的任何一个，不要难过.我们花了很长时间才开始运行它，但仍然很难对它进行编辑或创建类似它的新东西.幸运的是，它不需要经常更改，否则它将很快变得不值得麻烦".
但没有有效.
然后我的问题是:还有更好的方法吗?
除了存储过程解决方案外，我还可以解决其他问题，因为我意识到这可能不是解决之道.最好是，我想知道是否有人可以在存储过程中做得更好，但是如果不能，那么大家如何处理才能让用户使用ASP.NET对数据表进行动态排序(双向也是如此)?
感谢您阅读(或至少浏览了)这么长的问题！
PS:很高兴我没有展示存储过程的示例，该示例支持动态排序，动态过滤/列文本搜索，通过ROWNUMBER()OVER分页， AND 尝试. .抓住错误的事务回滚...庞然大物"甚至没有开始描述它们.
更新:
- 我想避免使用动态SQL .将字符串解析在一起并在其上运行EXEC会破坏许多首先具有存储过程的目的.有时我想知道，这样做的弊端是否值得，至少在这些特殊的动态排序情况下是不值得的.不过，每次执行这样的动态SQL字符串时，我总是感到肮脏—就像我仍然生活在Classic ASP世界中一样.
- 首先要存储过程的很多原因是出于安全性.我不会就安全性问题打电话，只是建议解决方案.使用SQL Server 2005，我们可以在架构级别上对单个存储过程设置权限(如果需要，可以基于每个用户)，然后直接拒绝对表的任何查询.批评这种方法的优缺点也许是另一个问题，但这不是我的决定.我只是主要代码猴子. :)
是的，这很痛苦，您的操作方式与我的操作类似:
order by
case when @SortExpr = 'CustomerName' and @SortDir = 'ASC'
then CustomerName end asc,
case when @SortExpr = 'CustomerName' and @SortDir = 'DESC'
then CustomerName end desc,
...
对我来说，这仍然比通过代码构建动态SQL好得多，这变成了DBA的可伸缩性和维护噩梦.
我从代码中所做的是重构分页和排序，因此至少在其中没有很多重复，其中填充了@SortExpr和@SortDir的值.
就SQL而言，在不同的存储过程之间保持设计和格式相同，因此在进行更改时至少是整洁且可识别的.
This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that really addresses what I see to be an incredibly common need in any Web or Windows application with a database back-end.
I speak of dynamic sorting. In my fantasy world, it should be as simple as something like:
ORDER BY @sortCol1, @sortCol2
This is the canonical example given by newbie SQL and Stored Procedure developers all over forums across the Internet. "Why isn't this possible?" they ask. Invariably, somebody eventually comes along to lecture them about the compiled nature of stored procedures, of execution plans in general, and all sorts of other reasons why it isn't possible to put a parameter directly into an ORDER BY clause.
I know what some of you are already thinking: "Let the client do the sorting, then." Naturally, this offloads the work from your database. In our case though, our database servers aren't even breaking a sweat 99% of the time and they aren't even multi-core yet or any of the other myriad improvements to system architecture that happen every 6 months. For this reason alone, having our databases handle sorting wouldn't be a problem. Additionally, databases are very good at sorting. They are optimized for it and have had years to get it right, the language for doing it is incredibly flexible, intuitive, and simple and above all any beginner SQL writer knows how to do it and even more importantly they know how to edit it, make changes, do maintenance, etc. When your databases are far from being taxed and you just want to simplify (and shorten!) development time this seems like an obvious choice.
Then there's the web issue. I've played around with JavaScript that will do client-side sorting of HTML tables, but they inevitably aren't flexible enough for my needs and, again, since my databases aren't overly taxed and can do sorting really really easily, I have a hard time justifying the time it would take to re-write or roll-my-own JavaScript sorter. The same generally goes for server-side sorting, though it is already probably much preferred over JavaScript. I'm not one that particularly likes the overhead of DataSets, so sue me.
But this brings back the point that it isn't possible — or rather, not easily. I've done, with prior systems, an incredibly hack way of getting dynamic sorting. It wasn't pretty, nor intuitive, simple, or flexible and a beginner SQL writer would be lost within seconds. Already this is looking to be not so much a "solution" but a "complication."
The following examples are not meant to expose any sort of best practices or good coding style or anything, nor are they indicative of my abilities as a T-SQL programmer. They are what they are and I fully admit they are confusing, bad form, and just plain hack.
We pass an integer value as a parameter to a stored procedure (let's call the parameter just "sort") and from that we determine a bunch of other variables. For example... let's say sort is 1 (or the default):
DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)
SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';
IF @sort = 1 -- Default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'asc';
SET @sortCol2 = @col2;
SET @dir2 = 'asc';
END
ELSE IF @sort = 2 -- Reversed order default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'desc';
SET @sortCol2 = @col2;
SET @dir2 = 'desc';
END
You can already see how if I declared more @colX variables to define other columns I could really get creative with the columns to sort on based on the value of "sort"... to use it, it usually ends up looking like the following incredibly messy clause:
ORDER BY
CASE @dir1
WHEN 'desc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir1
WHEN 'asc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END,
CASE @dir2
WHEN 'desc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir2
WHEN 'asc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END
Obviously this is a very stripped down example. The real stuff, since we usually have four or five columns to support sorting on, each with possible secondary or even a third column to sort on in addition to that (for example date descending then sorted secondarily by name ascending) and each supporting bi-directional sorting which effectively doubles the number of cases. Yeah... it gets hairy really quick.
The idea is that one could "easily" change the sort cases such that vehicleid gets sorted before the storagedatetime... but the pseudo-flexibility, at least in this simple example, really ends there. Essentially, each case that fails a test (because our sort method doesn't apply to it this time around) renders a NULL value. And thus you end up with a clause that functions like the following:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah
You get the idea. It works because SQL Server effectively ignores null values in order by clauses. This is incredibly hard to maintain, as anyone with any basic working knowledge of SQL can probably see. If I've lost any of you, don't feel bad. It took us a long time to get it working and we still get confused trying to edit it or create new ones like it. Thankfully it doesn't need changing often, otherwise it would quickly become "not worth the trouble."
Yet it did work.
My question is then: is there a better way?
I'm okay with solutions other than Stored Procedure ones, as I realize it may just not be the way to go. Preferably, I'd like to know if anyone can do it better within the Stored Procedure, but if not, how do you all handle letting the user dynamically sort tables of data (bi-directionally, too) with ASP.NET?
And thank you for reading (or at least skimming) such a long question!
PS: Be glad I didn't show my example of a stored procedure that supports dynamic sorting, dynamic filtering/text-searching of columns, pagination via ROWNUMBER() OVER, AND try...catch with transaction rollbacking on errors... "behemoth-sized" doesn't even begin to describe them.
Update:
- I would like to avoid dynamic SQL. Parsing a string together and running an EXEC on it defeats a lot of the purpose of having a stored procedure in the first place. Sometimes I wonder though if the cons of doing such a thing wouldn't be worth it, at least in these special dynamic sorting cases. Still, I always feel dirty whenever I do dynamic SQL strings like that — like I'm still living in the Classic ASP world.
- A lot of the reason we want stored procedures in the first place is for security. I don't get to make the call on security concerns, only suggest solutions. With SQL Server 2005 we can set permissions (on a per-user basis if need be) at the schema level on individual stored procedures and then deny any queries against the tables directly. Critiquing the pros and cons of this approach is perhaps for another question, but again it's not my decision. I'm just the lead code monkey. :)
Yeah, it's a pain, and the way you're doing it looks similar to what I do:
order by
case when @SortExpr = 'CustomerName' and @SortDir = 'ASC'
then CustomerName end asc,
case when @SortExpr = 'CustomerName' and @SortDir = 'DESC'
then CustomerName end desc,
...
This, to me, is still much better than building dynamic SQL from code, which turns into a scalability and maintenance nightmare for DBAs.
What I do from code is refactor the paging and sorting so I at least don't have a lot of repetition there with populating values for @SortExpr and @SortDir.
As far as the SQL is concerned, keep the design and formatting the same between different stored procedures, so it's at least neat and recognizable when you go in to make changes.
这篇关于SQL存储过程中的动态排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
