SQL 存储过程中的动态排序 [英] Dynamic Sorting within SQL Stored Procedures
问题描述
这是我过去花费数小时研究的一个问题.在我看来，现代 RDBMS 解决方案应该解决这个问题，但到目前为止我已经在任何具有数据库后端的 Web 或 Windows 应用程序中，没有发现任何可以真正满足我认为非常普遍的需求的内容.
我说的是动态排序.在我的幻想世界里，它应该像这样简单:
ORDER BY @sortCol1, @sortCol2这是 SQL 新手和 存储过程 开发人员在整个论坛上给出的规范示例互联网.为什么这不可能?"他们问.总会有人最终来向他们讲授存储过程的编译性质、一般的执行计划，以及无法将参数直接放入 ORDER BY 子句.
我知道你们中的一些人已经在想:那么，让客户进行分类."自然，这会从您的数据库中卸载工作.但在我们的案例中，我们的数据库服务器甚至 99% 的时间都没有出汗，它们甚至还不是多核的，也不是每 6 个月发生一次的系统架构的任何其他无数改进.仅出于这个原因，让我们的数据库处理排序就不是问题.此外，数据库非常擅长排序.他们针对它进行了优化，并且已经用了很多年的时间来做对，这样做的语言非常灵活、直观和简单，最重要的是，任何 SQL 初学者都知道如何去做，更重要的是，他们知道如何编辑它，进行更改、维护等.当您的数据库远离负担而您只想简化(并缩短！)开发时间时，这似乎是一个显而易见的选择.
然后是网络问题.我已经尝试过使用 JavaScript 对 HTML 表进行客户端排序，但它们不可避免地不够灵活，无法满足我的需求，而且，因为我的数据库并没有过度征税并且可以真正进行排序 很容易，我很难证明重写或滚动我自己的 JavaScript 排序器所需的时间是合理的.服务器端排序通常也是如此，尽管它可能已经比 JavaScript 更受欢迎.我不是一个特别喜欢 DataSets 开销的人，所以起诉我.
但这又带回了不可能的观点—或者更确切地说，并不容易.我已经使用以前的系统完成了一种令人难以置信的动态排序方法.它既不漂亮，也不直观、简单或灵活，而且初学者 SQL 编写器会在几秒钟内迷失.这已经不是一个解决方案"，而是一个复杂化".
<小时>以下示例无意公开任何类型的最佳实践或良好的编码风格或任何其他内容，也不表明我作为 T-SQL 程序员的能力.它们就是它们的样子，我完全承认它们令人困惑，形式不佳，而且只是简单的黑客.
我们将一个整数值作为参数传递给存储过程(让我们将参数称为排序")，并从中确定一堆其他变量.例如...假设 sort 为 1(或默认值):
DECLARE @sortCol1 AS varchar(20)声明 @sortCol2 为 varchar(20)声明 @dir1 为 varchar(20)声明 @dir2 为 varchar(20)声明 @col1 为 varchar(20)声明 @col2 为 varchar(20)SET @col1 = 'storagedatetime';SET @col2 = 'vehicleid';IF @sort = 1 -- 默认排序.开始设置@sortCol1 = @col1;SET @dir1 = 'asc';设置@sortCol2 = @col2;SET @dir2 = 'asc';结尾ELSE IF @sort = 2 -- 倒序默认排序.开始设置@sortCol1 = @col1;SET @dir1 = 'desc';设置@sortCol2 = @col2;SET @dir2 = 'desc';结尾您已经可以看到，如果我声明更多 @colX 变量来定义其他列，我真的可以根据排序"的值对列进行排序，从而发挥创意...使用它，通常最终会查找就像以下令人难以置信的凌乱条款:
ORDER BY案例@dir1WHEN 'desc' THEN案例@sortCol1WHEN @col1 THEN [storagedatetime]当@col2 THEN [车辆编号]结尾结束描述，案例@dir1当 'asc' THEN案例@sortCol1WHEN @col1 THEN [storagedatetime]当@col2 THEN [车辆编号]结尾结尾，案例@dir2WHEN 'desc' THEN案例@sortCol2WHEN @col1 THEN [storagedatetime]当@col2 THEN [车辆编号]结尾结束描述，案例@dir2当 'asc' THEN案例@sortCol2WHEN @col1 THEN [storagedatetime]当@col2 THEN [车辆编号]结尾结尾显然，这是一个非常精简的示例.真正的东西，因为我们通常有四到五列来支持排序，每一列可能还有第二列甚至第三列要排序(例如日期降序然后按名称升序排序)并且每个支持双定向排序，有效地将案例数量加倍.是的...它变得毛茸茸的非常快.
我们的想法是，人们可以轻松地"更改排序情况，以便在 storagedatetime 之前对 Vehicleid 进行排序……但是，至少在这个简单的示例中，伪灵活性真的到此为止了.本质上，每个测试失败的情况(因为我们的排序方法这次不适用于它)呈现一个 NULL 值.因此，您最终会得到一个功能如下的子句:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah你懂的.它之所以有效，是因为 SQL Server 有效地忽略了按 by 子句顺序排列的空值.这是非常难以维护的，因为任何具有 SQL 基本工作知识的人都可能会看到.如果我失去了你们中的任何一个，请不要难过.我们花了很长时间才让它工作，我们仍然在尝试编辑它或创建类似的新文件时感到困惑.幸好它不需要经常更换，否则很快就会变得不值得麻烦".
然而它确实起作用了.
<小时>我的问题是:有更好的方法吗?
我对存储过程以外的解决方案没意见，因为我意识到这可能不是正确的方法.最好，我想知道是否有人可以在存储过程中做得更好，但如果不能，你们如何处理让用户使用 ASP.NET 动态排序数据表(也是双向的)?
感谢您阅读(或至少略读)这么长的问题！
PS:很高兴我没有展示支持动态排序、动态过滤/列文本搜索、通过 ROWNUMBER() OVER 进行分页的存储过程示例，AND 尝试...catch 事务回滚错误……庞然大物"甚至没有开始描述它们.
<小时>更新:
- 我想避免使用动态 SQL.一起解析一个字符串并在其上运行一个 EXEC，首先违背了拥有存储过程的许多目的.有时我想知道这样做的缺点是否不值得，至少在这些特殊的动态排序情况下.不过，每当我做这样的动态 SQL 字符串时，我总是觉得很脏—就像我仍然生活在经典 ASP 世界中一样.
- 我们首先需要存储过程的很多原因都是为了安全.我不能就安全问题提出要求，只能提出解决方案.使用 SQL Server 2005，我们可以在架构级别对各个存储过程设置权限(如果需要，可以在每个用户的基础上)，然后直接拒绝对表的任何查询.批评这种方法的利弊也许是另一个问题，但这又不是我的决定.我只是首席代码猴子.:)
是的，这很痛苦，而且你做的方式看起来和我做的很相似:
order by@SortExpr = 'CustomerName' 和 @SortDir = 'ASC' 的情况然后 CustomerName 以 asc 结尾，@SortExpr = 'CustomerName' 和 @SortDir = 'DESC' 的情况然后 CustomerName 结束说明，...对我来说，这仍然比从代码构建动态 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屋！
