是否可以在SQL Server中给定存储过程参数的任何列进行排序？

I was looking into sorting tables by a column designated given some input, and from what I've found, there is no easy way to do this. The best I've found is a switch statement:

我正在考虑通过指定给出一些输入的列对表进行排序,并且从我发现的内容来看,没有简单的方法可以做到这一点。我找到的最好的是一个switch语句:

SELECT Column1, Column2, Column3, Column4
FROM Table
ORDER BY CASE WHEN @OrderBY = 'Column1' THEN Column1
              WHEN @OrderBY = 'Column2' THEN Column2
              WHEN @OrderBY = 'Column3' THEN Column3
              WHEN @OrderBY = 'Column4' THEN Column4

Is it possible to do this without having a CASE statement like that? If the table gets bigger and more columns need to be sorted by, this could become messy.

没有这样的CASE声明是否可以做到这一点?如果表变大并且需要对更多列进行排序,则可能会变得混乱。

The only way I've been able to do this is by just concatenating a big SQL string, which sort of defeats the advantages of Stored Procedures, and makes the SQL hard to write and maintain.

我能够做到这一点的唯一方法就是连接一个大的SQL字符串,这会破坏存储过程的优点,并使SQL难以编写和维护。

5 个解决方案

#1

You have two choices:

你有两个选择:

As you have implemented above

如上所述
Or generate dynamic sql and execute using sp_executesql

或者生成动态sql并使用sp_executesql执行

#2

I generally convert the stored procedure to a function that returns a table ( so you can select FROM it ... and add the dynamic order by columns to it in the application code:

我通常将存储过程转换为返回表的函数(因此您可以选择FROM ...并在应用程序代码中按列添加动态顺序:

Select
    *
From
   myTableFUnction()
Order by
   1, 2, 3, 6  <-- defined by application code in the SQL for the query

Ron

#3

You already write the correct syntax:

您已经编写了正确的语法:

SELECT Column1, Column2, Column3 
FROM SOME_TABLE
ORDER BY 1,2,3

try it

#4

The RANK feature of SQL Server and Oracle can improve performance and makes the code a little cleaner:

SQL Server和Oracle的RANK功能可以提高性能并使代码更清晰:

SQL:

DECLARE @column varchar(10)

SET @column = 'D'

SELECT *
FROM Collection.Account AS A
ORDER BY 
      CASE 
            WHEN @column = 'A' THEN (RANK() OVER(ORDER BY A.Code ASC))
            WHEN @column = 'D' THEN (RANK() OVER(ORDER BY A.Code DESC))
      END

#5

-2

In this case, unless you have an extremely large dataset and you need to leverage the power of the database server (thin client, weak client machine, etc), it is best to sort within the client.

在这种情况下,除非您拥有非常大的数据集,并且需要利用数据库服务器(瘦客户端,弱客户端计算机等)的强大功能,否则最好在客户端中进行排序。

#1