SQL 存储过程中的动态排序
这是我过去花了几个小时研究的问题。在我看来，这是现代 关系数据库解决方案应该解决的问题，但是到目前为止，我还没有发现任何东西能够真正解决我所看到的在任何具有数据库后端的 Web 或 Windows 应用程序中非常普遍的需求。
我说的是动态排序。在我的幻想世界里，它应该像下面这样简单:
ORDER BY @sortCol1, @sortCol2
这是互联网上所有论坛上新手 SQL 和 存储过程开发人员给出的典型示例。“为什么这不可能?”他们会问。最终总会有人来教他们存储过程的编译本质，执行计划的一般性，以及为什么不可能直接将参数放入 ORDER BY子句的各种其他原因。
我知道你们中的一些人已经在想: “那么，让客户来排序吧。”自然地，这会从数据库中卸载工作。然而，在我们的例子中，我们的数据库服务器99% 的时间都没有出汗，它们甚至还没有多核，或者其他每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
显然，这是一个非常简单的例子。真正的内容，因为我们通常有四到五列来支持排序，每一列都可能有第二列甚至第三列来进行排序(例如，日期降序然后按名称升序进行第二次排序) ，每一列都支持双向排序，这有效地使案例数量翻了一番。是啊，很快就会变得毛茸茸的。
这个想法是人们可以“容易地”改变排序情况，比如 vehicle leid 在存储时间之前被排序... 但是伪灵活性，至少在这个简单的例子中，真的到此为止了。实际上，每个测试失败的情况(因为这次我们的排序方法不适用于它)都呈现一个 NULL 值。这样你就得到了一个子句，它的功能如下:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah
你懂的。它之所以能够工作，是因为 SQLServer 有效地忽略了按子句顺序排列的空值。这是非常难以维护的，任何具有 SQL 基本工作知识的人都可以看到这一点。如果我失去了你们中的任何一个，不要难过。我们花了很长时间才让它工作，我们仍然感到困惑，试图编辑或创建新的喜欢它。谢天谢地，它不需要经常改变，否则很快就会变得“不值得麻烦。”
但 是的还是有用的。
我的问题是: 还有更好的办法吗？
我对存储过程以外的解决方案没有意见，因为我意识到这可能不是正确的方法。最好，我想知道是否有人可以在存储过程中做得更好，但如果没有，如何处理让用户使用 ASP.NET 对数据表(也是双向的)进行动态排序？
感谢您阅读(或至少略读)这么长的问题！
PS: 很高兴我没有展示一个支持动态排序、动态过滤/列的文本搜索、 ROWNUMBER () OVER 分页、 还有 try 分页的存储过程示例... ... 错误的事务回滚捕获... ... “庞然大物般大小”甚至都不足以描述它们。
更新:
- 我想要 避免使用动态 SQL。一起解析一个字符串并在其上运行 EXEC 违背了首先使用存储过程的许多目的。有时候我会想，这样做的弊端是否值得，至少在这些特殊的动态排序情况下是这样的。尽管如此，每当我使用动态 SQL 字符串时，我总是感觉很脏，就像我仍然生活在经典 ASP 世界中一样。
- 我们首先需要存储过程的很多原因是为了 保安。我不能就安全问题发号施令，只能提出解决方案。使用 SQLServer2005，我们可以在模式级别上设置单个存储过程的权限(如果需要的话，以每个用户为基础) ，然后直接拒绝对表的任何查询。批评这种方法的优点和缺点也许是另一个问题，但这又不是我的决定。我只是个负责编程的猴子。:)
