SQL 存储过程中的动态排序

Dynamic Sorting within SQL Stored Procedures

提问by Sean Hanley

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 RDBMSsolutions 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.

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

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 Proceduredevelopers 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 BYclause.

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

---

---

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 verygood 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.

我知道你们中的一些人已经在想：“那么，让客户进行分类。” 自然，这会从您的数据库中卸载工作。但在我们的案例中，我们的数据库服务器甚至 99% 的时间都没有出汗，它们甚至还不是多核的，也不是每 6 个月发生一次的系统架构的任何其他无数改进。仅出于这个原因，让我们的数据库处理排序就不是问题。此外，数据库非常善于整理。他们针对它进行了优化，并且已经用了很多年的时间来实现它，这样做的语言非常灵活、直观和简单，最重要的是，任何 SQL 初学者都知道如何去做，更重要的是他们知道如何编辑它，进行更改，进行维护等。当您的数据库远离负担而您只想简化（并缩短！）开发时间时，这似乎是一个显而易见的选择。

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 reallyeasily, 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.

然后是网络问题。我已经尝试过使用 JavaScript 对 HTML 表进行客户端排序，但它们不可避免地不够灵活，无法满足我的需求，而且，由于我的数据库没有过度负担并且可以非常轻松地进行排序，我很难证明重新编写或滚动我自己的 JavaScript 排序器所需的时间是合理的。服务器端排序通常也是如此，尽管它可能已经比 JavaScript 更受欢迎。我不是一个特别喜欢 DataSets 开销的人，所以起诉我。

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."

但这又说明了这是不可能的——或者更确切地说，不容易。我已经使用以前的系统完成了一种令人难以置信的动态排序方法。它既不漂亮，也不直观、简单或灵活，而且初学者 SQL 编写者会在几秒钟内迷失方向。这已经不是一个“解决方案”，而是一个“复杂化”。

---

---

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.

以下示例无意公开任何类型的最佳实践或良好的编码风格或任何内容，也不表明我作为 T-SQL 程序员的能力。他们就是这样，我完全承认他们令人困惑，形式不佳，而且只是简单的黑客。

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):

我们将一个整数值作为参数传递给存储过程（让我们将参数称为“排序”），并从中确定一堆其他变量。例如...假设 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

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:

您已经可以看到，如果我声明了更多 @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

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:

这个想法是，人们可以“轻松地”更改排序情况，以便在 storagedatetime 之前对 Vehicleid 进行排序……但是，至少在这个简单的示例中，伪灵活性确实到此为止。本质上，每个未通过测试的情况（因为我们的 sort 方法这次不适用于它）呈现一个 NULL 值。因此，您最终会得到一个功能如下的子句：

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."

你明白了。它起作用是因为 SQL Server 有效地忽略了按 by 子句顺序排列的空值。这是非常难以维护的，因为任何具有 SQL 基本工作知识的人都可能会看到。如果我失去了你们中的任何一个，请不要难过。我们花了很长时间才让它工作，我们仍然在尝试编辑它或创建类似的新文件时感到困惑。值得庆幸的是，它不需要经常更改，否则很快就会变得“不值得麻烦”。

Yet it didwork.

然而它确实奏效了。

---

---

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?

我对存储过程以外的解决方案没有意见，因为我意识到这可能不是要走的路。最好，我想知道是否有人可以在存储过程中做得更好，但如果不能，你们如何处理让用户使用 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, ANDtry...catch with transaction rollbacking on errors... "behemoth-sized" doesn't even begin to describe them.

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

---

---

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. :)

• 我想避免动态 SQL。一起解析一个字符串并在其上运行一个 EXEC，首先就违背了拥有存储过程的许多目的。有时我想知道这样做的缺点是否不值得，至少在这些特殊的动态排序情况下。尽管如此，每当我做这样的动态 SQL 字符串时，我总是觉得很脏——就像我仍然生活在经典的 ASP 世界中一样。
• 我们首先需要存储过程的很多原因是为了安全。我不能就安全问题提出要求，只能提出解决方案。使用 SQL Server 2005，我们可以在架构级别上对各个存储过程设置权限（如果需要，可以在每个用户的基础上），然后直接拒绝对表的任何查询。批评这种方法的利弊也许是另一个问题，但这又不是我的决定。我只是首席代码猴子。:)

回答by Eric Z Beard

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.

对我来说，这仍然比从代码构建动态 SQL 好得多，后者变成了 DBA 的可扩展性和维护噩梦。

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 @SortExprand @SortDir.

我从代码中所做的是重构分页和排序，所以我至少不会在那里重复填充@SortExprand 的值@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而言，在不同的存储过程之间保持设计和格式相同，这样在您进行更改时至少是整洁和可识别的。

回答by Jason DeFontes

This approach keeps the sortable columns from being duplicated twice in the order by, and is a little more readable IMO:

这种方法可以防止可排序列在 order by 中重复两次，并且在 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

回答by jop

Dynamic SQL is still an option. You just have to decide whether that option is more palatable than what you currently have.

动态 SQL 仍然是一种选择。您只需要决定该选项是否比您目前拥有的更可口。

Here is an article that shows that: http://www.4guysfromrolla.com/webtech/010704-1.shtml.

这是一篇文章，表明：http: //www.4guysfromrolla.com/webtech/010704-1.shtml。

回答by Ron Savage

My applications do this a lot but they are all dynamically building the SQL. However, when I deal with stored procedures I do this:

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

1. Make the stored procedure a function that returns a table of your values - no sort.
2. Then in your application code do a select * from dbo.fn_myData() where ... order by ...so you can dynamically specify the sort order there.

1. 使存储过程成为返回值表的函数 - 没有排序。
2. 然后在您的应用程序代码中执行一个操作，select * from dbo.fn_myData() where ... order by ...以便您可以在那里动态指定排序顺序。

Then at least the dynamic part is in your application, but the database is still doing the heavy lifting.

那么至少动态部分在您的应用程序中，但数据库仍在做繁重的工作。

回答by dave

A stored procedure technique (hack?) I've used to avoid dynamic SQL for certain jobs is to have a unique sort column. I.e.,

我用来避免某些作业的动态 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

This one is easy to beat into submission -- you can concat fields in your mySort column, reverse the order with math or date functions, etc.

这个很容易提交——您可以连接 mySort 列中的字段，使用数学或日期函数颠倒顺序等。

Preferably though, I use my asp.net gridviews or other objects with build-in sorting to do the sorting for me AFTER retrieving the data fro Sql-Server. Or even if it's not built-in -- e.g., datatables, etc. in asp.net.

不过，我最好使用我的 asp.net gridviews 或其他具有内置排序功能的对象在从 Sql-Server 检索数据后为我进行排序。或者即使它不是内置的——例如，asp.net 中的数据表等。

回答by Steven A. Lowe

There may be a third option, since your server has lots of spare cycles - use a helper procedure to do the sorting via a temporary table. Something like

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

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

Caveat: I haven't tested this, but it "should" work in SQL Server 2005 (which will create a temporary table from a result set without specifying the columns in advance.)

警告：我还没有测试过这个，但它“应该”在 SQL Server 2005 中工作（它将从结果集中创建一个临时表，而无需事先指定列。）

回答by Matt Rogish

There's a couple of different ways you can hack this in.

有几种不同的方法可以破解它。

Prerequisites:

先决条件：

1. Only one SELECT statement in the sp
2. Leave out any sorting (or have a default)

1. sp中只有一个SELECT语句
2. 省去任何排序（或有一个默认）

Then insert into a temp table:

然后插入临时表：

create table #temp ( your columns )

insert #temp
exec foobar

select * from #temp order by whatever

Method #2: set up a linked server back to itself, then select from this using openquery: http://www.sommarskog.se/share_data.html#OPENQUERY

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

回答by Hank Gay

At some point, doesn't it become worth it to move away from stored procedures and just use parameterized queries to avoid this sort of hackery?

在某些时候，放弃存储过程并仅使用参数化查询来避免这种黑客行为是否值得？

回答by D.S.

I agree, use client side. But it appears that is not the answer you want to hear.

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

So, it is perfect the way it is. I don't know why you would want to change it, or even ask "Is there a better way." Really, it should be called "The Way". Besides, it seems to work and suit the needs of the project just fine and will probably be extensible enough for years to come. Since your databases aren't taxed and sorting is really really easyit should stay that way for years to come.

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

I wouldn't sweat it.

我不会出汗的。

回答by dotjoe

When you are paging sorted results, dynamic SQL is a good option. If you're paranoid about SQL injection you can use the column numbers instead of the column name. I've done this before using negative values for descending. Something like this...

当您对排序结果进行分页时，动态 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

Then you just need to make sure the number is inside 1 to # of columns. You could even expand this to a list of column numbers and parse that into a table of ints using a function like this. Then you would build the order by clause like so...

然后你只需要确保数字在 1 到 # 列之间。你甚至可以扩大这列数的列表，并解析成使用类似的功能INTS的表此。然后你会像这样构建 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

One drawback is you have to remember the order of each column on the client side. Especially, when you don't display all the columns or you display them in a different order. When the client wants to sort, you map the column names to the column order and generate the list of ints.

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