我过去花了数小时研究这个问题。在我看来，现代RDBMS解决方案应该解决该问题，但是到目前为止，我还没有发现任何能够真正解决我认为在任何具有数据库后端的Web或者Windows应用程序中非常普遍的需求的东西。

我说的是动态排序。在我的幻想世界中，它应该像下面这样简单：

ORDER BY @sortCol1, @sortCol2

这是新手SQL和存储过程开发人员在整个Internet论坛上给出的规范示例。 "为什么这不可能？"他们问。最终，总是有人总会向他们讲解存储过程的编译特性，一般的执行计划以及无法将参数直接放入ORDER BY子句的各种其他原因。

我知道你们中有些人已经在想："那么，让客户来进行分类。"自然，这会减轻数据库的工作量。但是在我们的案例中，我们的数据库服务器甚至在99％的时间内都无法中断工作，它们甚至还不是多核的，也不是每6个月进行一次其他的系统架构无数次改进。仅出于这个原因，让我们的数据库处理排序就不会有问题。此外，数据库非常擅长排序。他们为此进行了优化，并且已经花了好几年的时间来实现它，其实现语言非常灵活，直观，简单，最重要的是，任何SQL初学者都知道该怎么做，更重要的是，他们知道如何对其进行编辑，进行更改，进行维护等。当数据库需要负担很多费用而我们只想简化(并缩短！)开发时间时，这似乎是一个显而易见的选择。

然后是网络问题。我一直在使用JavaScript进行HTML表格的客户端排序，但是它们不可避免地不够灵活，无法满足我的需求；而且，由于我的数据库没有负担过多，并且可以非常轻松地进行排序，因此我很难证明重新编写JavaScript或者自己编写JavaScript分类器所花费的时间。服务器端排序通常也是如此，尽管它可能已经比JavaScript更受青睐。我不是特别喜欢DataSet开销的人，所以提起诉讼。

但这又回到了不可能或者不容易做到的地步。在以前的系统中，我已经完成了获得动态排序的令人难以置信的破解方式。它不是很漂亮，也不是直观，简单或者灵活的，初学者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

显然，这是一个非常简单的例子。真正的东西，因为我们通常有4或者5列支持排序，每列除此以外还可能有第二列或者什至是第三列进行排序(例如，日期递减，然后按名称升序进行第二排序)，并且每一个都支持双向定向排序可有效地将案件数量加倍。是的...真的很快就长了毛。

这个想法是，人们可以"轻松地"更改排序条件，以使caridid在storagedatetime之前被排序...但是，至少在这个简单示例中，伪柔韧性才真正到此结束。本质上，每个未通过测试的案例(因为这次我们的sort方法都不适用)会呈现NULL值。因此，我们最终得到了一个功能如下的子句：

ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah

你明白了。之所以起作用，是因为SQL Server有效地忽略了by子句中的空值。这很难维护，因为任何具有SQL基本知识的人都可以看到。如果我失去了你们中的任何一个，不要难过。我们花了很长时间才开始运行它，但仍然很难对它进行编辑或者创建像它这样的新文件。值得庆幸的是，它不需要经常进行更改，否则它将很快变得"不值得麻烦"。

然而它确实起作用了。

我的问题是：还有更好的方法吗？

除了存储过程解决方案外，我还可以解决其他问题，因为我意识到这可能不是解决方案。最好是，我想知道是否有人可以在存储过程中做得更好，但是如果不能，那么大家如何处理如何让用户使用ASP.NET对数据表进行动态排序(也双向)？

并感谢我们阅读(或者至少浏览)这么长的问题！

PS：很高兴我没有展示存储过程的示例，该示例支持动态排序，动态过滤/列的文本搜索，通过ROWNUMBER()OVER进行分页，并尝试...捕获错误时的事务回滚... "庞然大物"甚至没有开始描述它们。

更新：

• 我想避免使用动态SQL。将字符串解析在一起并在其上运行EXEC会破坏许多首先具有存储过程的目的。有时我想知道，这样做的弊端是否值得，至少在这些特殊的动态排序情况下是不值得的。尽管如此，每当我处理动态SQL字符串时，就像我仍然生活在Classic ASP世界中一样，我总是感到肮脏。
• 首先，我们希望存储过程的许多原因是出于安全性考虑。我不会就安全性问题打电话，只是建议解决方案。使用SQL Server 2005，我们可以在架构级别上对单个存储过程设置权限(如果需要，可以基于每个用户)，然后直接拒绝对表的任何查询。批评这种方法的优缺点也许是另一个问题，但这不是我的决定。我只是主要代码猴子。 :)

解决方案

如何对显示结果的东西(网格，报表等)而不是对SQL进行排序？

编辑：

为了弄清楚这个答案早些时候被否决的原因，我将详细说明一下...

我们说过我们了解客户端排序，但想避免这种情况。那是你的电话。

不过，我想指出的是，通过在客户端进行操作，我们可以一次拉入数据，然后使用它，但是我们想要-而不是每次在服务器上来回往返多次排序被更改。

SQL Server现在无需收税，这太棒了。不应该这样但是，仅仅因为它没有过载，并不意味着它将永远保持这种状态。

如果我们正在使用任何较新的ASP.NET东西来在Web上显示，则其中很多东西已经被使用。

值得为每个存储过程添加大量代码以处理排序吗？同样，电话。

我不是最终负责支持它的人。但是，请考虑一下在存储过程使用的各种数据集中添加/删除列(需要对CASE语句进行修改)或者突然而不是按两列进行排序时，用户认为需要三列会涉及什么？我们现在可以更新使用此方法的每个存储过程。

对我来说，获得一个可行的客户端解决方案并将其应用于少数面向用户的数据显示并完成它是值得的。如果添加了新列，则已经处理完毕。如果用户要按多列排序，则可以按两列或者二十列进行排序。

在某个时候，离开存储过程而仅使用参数化查询来避免这种黑客行为是否值得？

仍然可以选择动态SQL。我们只需要确定该选项是否比我们当前拥有的选项更可口。

这是一篇显示内容的文章：http://www.4guysfromrolla.com/webtech/010704-1.shtml。

是的，这很痛苦，操作方式与我的操作类似：

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而言，在不同的存储过程之间保持设计和格式相同，因此，当我们进行更改时，它至少是整洁且可识别的。

可能还有第三种选择，因为服务器有很多空闲周期，请使用帮助程序通过临时表进行排序。就像是

create procedure uspCallAndSort
(
    @sql varchar(2048),        --exec dbo.uspSomeProcedure arg1,'arg2',etc.
    @sortClause varchar(512)    --comma-delimited field list
)
AS
insert into #tmp EXEC(@sql)
declare @msql varchar(3000)
set @msql = 'select * from #tmp order by ' + @sortClause
EXEC(@msql)
drop table #tmp
GO

警告：我还没有测试过，但是它"应该"在SQL Server 2005中工作(它将在不预先指定列的情况下从结果集中创建一个临时表。)

我同意，请使用客户端。但这似乎不是我们想要听到的答案。

因此，它是完美的方式。我不知道我们为什么要更改它，甚至不问"有没有更好的方法"。确实，它应该被称为" The Way"。此外，它似乎可以很好地满足项目的需求，并且可能在未来几年内具有足够的可扩展性。由于数据库无需收税，而且排序确实非常容易，因此应该在未来几年保持这种状态。

我不会流汗。

我的应用程序经常执行此操作，但是它们都是动态构建SQL。但是，当我处理存储过程时，我这样做：

• 使存储过程成为一个返回值表的函数-不进行排序。
• 然后在应用程序代码中，从dbo.fn_myData()中选择select * where ... order by ...，以便我们可以在那里动态指定排序顺序。

然后，至少动态部分在应用程序中，但是数据库仍在繁重的工作中。

我们可以通过几种不同的方式来破解它。

准备工作：

• sp中只有一个SELECT语句
• 忽略任何排序(或者使用默认设置)

然后插入到临时表中：

create table #temp ( your columns )

insert #temp
exec foobar

select * from #temp order by whatever

方法2：将链接服务器设置回自身，然后使用openquery从中选择：
http://www.sommarskog.se/share_data.html#OPENQUERY

我不知道这种解决方案可能仅适用于.NET。

我使用SQL order by子句中的初始排序顺序将数据提取到C中，然后将该数据放入DataView中，将其缓存在Session变量中，然后使用其构建页面。

当用户单击列标题进行排序(或者页面或者过滤器)时，我不会返回数据库。相反，我返回到缓存的DataView并将其" Sort"属性设置为动态构建的表达式，就像动态SQL一样。 (我使用" RowFilter"属性以相同的方式进行过滤)。

我们可以在http://ifdefined.com/btnet/bugs.aspx的我的应用程序BugTracker.NET演示中看到/感觉它能正常工作。

除非必要，否则应避免对SQL Server进行排序。为什么不在应用服务器或者客户端上排序？ .NET泛型也可以进行出色的排序

这种方法可防止将可排序的列按顺序重复两次，并且IMO更具可读性：

SELECT
  s.*
FROM
  (SELECT
    CASE @SortCol1
      WHEN 'Foo' THEN t.Foo
      WHEN 'Bar' THEN t.Bar
      ELSE null
    END as SortCol1,
    CASE @SortCol2
      WHEN 'Foo' THEN t.Foo
      WHEN 'Bar' THEN t.Bar
      ELSE null
    END as SortCol2,
    t.*
  FROM
    MyTable t) as s
ORDER BY
  CASE WHEN @dir1 = 'ASC'  THEN SortCol1 END ASC,
  CASE WHEN @dir1 = 'DESC' THEN SortCol1 END DESC,
  CASE WHEN @dir2 = 'ASC'  THEN SortCol2 END ASC,
  CASE WHEN @dir2 = 'DESC' THEN SortCol2 END DESC

当对分页的结果进行分页时，动态SQL是一个不错的选择。如果我们对SQL注入感到疑惑，可以使用列号代替列名。在使用负值进行降序之前，我已经完成了此操作。像这样的东西

declare @o int;
set @o = -1;

declare @sql nvarchar(2000);
set @sql = N'select * from table order by ' + 
    cast(abs(@o) as varchar) + case when @o < 0 then ' desc' else ' asc' end + ';'

exec sp_executesql @sql

然后，我们只需要确保数字在1到1列之内即可。我们甚至可以将其扩展为列号列表，然后使用类似的函数将其解析为一个整数表。然后，我们将像这样构建order by子句...

declare @cols varchar(100);
set @cols = '1 -2 3 6';

declare @order_by varchar(200)

select @order_by = isnull(@order_by + ', ', '') + 
        cast(abs(number) as varchar) + 
        case when number < 0 then ' desc' else '' end
from dbo.iter_intlist_to_tbl(@cols) order by listpos

print @order_by

缺点之一是我们必须记住客户端上每个列的顺序。特别是，当我们不显示所有列或者以不同顺序显示它们时。当客户想要排序时，可以将列名称映射到列顺序并生成整数列表。

我用来避免某些作业使用动态SQL的存储过程技术(hack？)是具有唯一的排序列。 IE。，

SELECT
   name_last,
   name_first,
   CASE @sortCol WHEN 'name_last' THEN [name_last] ELSE 0 END as mySort
FROM
   table
ORDER BY 
    mySort

这很容易提交-我们可以在mySort列中合并字段，使用数学或者日期函数颠倒顺序，等等。

不过，最好是，我将asp.net gridviews或者其他对象与内置排序一起使用，以便在从Sql-Server检索数据之后为我进行排序。或者即使它不是内置的-例如，asp.net中的数据表等。