SQL “Order By”使用列名参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13844678/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Order By" using a parameter for the column name
提问by Emad-ud-deen
We would like to use a parameter in the "Order By" clause of a query or stored procedure created with the Visual Studio DataSet Designer.
我们希望在使用 Visual Studio 数据集设计器创建的查询或存储过程的“Order By”子句中使用参数。
Example:
例子：
FROM TableName
WHERE (Forename LIKE '%' + @SearchValue + '%') OR
(Surname LIKE '%' + @SearchValue + '%') OR
(@SearchValue = 'ALL')
ORDER BY @OrderByColumn
This error is displayed:
显示此错误：
Variables are only allowed when ordering by an expression referencing
a column name.
回答by Branko Dimitrijevic
You should be able to do something like this:
你应该能够做这样的事情：
SELECT *
FROM
TableName
WHERE
(Forename LIKE '%' + @SearchValue + '%') OR
(Surname LIKE '%' + @SearchValue + '%') OR
(@SearchValue = 'ALL')
ORDER BY
CASE @OrderByColumn
WHEN 1 THEN Forename
WHEN 2 THEN Surname
END;
- Assign 1 to
@OrderByColumnto sort onForename. - Assign 2 to sort on
Surname. - Etc... you can expand this scheme to arbitrary number of columns.
- 分配 1 以
@OrderByColumn对 进行排序Forename。 - 分配 2 对 进行排序
Surname。 - 等等...您可以将此方案扩展到任意数量的列。
Be careful about performance though. These kinds of constructs may interfere with query optimizer's ability to find an optimal execution plan. For example, even if Forenameis covered by index, query may still require the full sort instead of just traversing the index in order.
不过要注意性能。这些类型的构造可能会干扰查询优化器找到最佳执行计划的能力。例如，即使Forename被索引覆盖，查询仍可能需要完整排序而不是仅按顺序遍历索引。
If that is the case, and you can't live with the performance implications, it may be necessary to have a separate version of the query for each possible sort order, complicating things considerably client-side.
如果是这种情况，并且您无法忍受性能影响，则可能需要为每个可能的排序顺序使用单独的查询版本，这会使客户端的事情变得相当复杂。
回答by 3BK
I know that I'm coming in to this thread way late, but I just want to post this in case anyone else has a similar problem.
我知道我很晚才进入这个线程，但我只是想发布这个以防其他人有类似的问题。
The issue seems to occur when you try to perform an ORDER BYdirectly on the parameter, because SQL Server expects you to provide a number (1 for the first field, 2 for the second, and so on...), or a column name presented either as an identifier (MyField or "MyField") or a string ('MyField').
当您尝试ORDER BY直接对参数执行 an 时，似乎会出现此问题，因为 SQL Server 期望您提供一个数字（第一个字段为 1，第二个字段为 2，依此类推...），或显示的列名作为标识符（MyField 或“MyField”）或字符串（'MyField'）。
For example:
例如：
DECLARE @ORDERBY AS NVARCHAR(20)
;
SELECT @ORDERBY = :Param1 --(Supposing that the user enters 'MyField')
;
SELECT TOP 1 *
FROM MyTable
ORDER BY @ORDERBY DESC
;
You get the following error:
您收到以下错误：
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. (SQLSTATE=42000) (1008) (Severity=16)
由 ORDER BY 编号 1 标识的 SELECT 项包含一个变量，作为标识列位置的表达式的一部分。仅当按引用列名的表达式排序时才允许使用变量。(SQLSTATE=42000) (1008)（严重性=16）
If you write out the query manually in any one of the described ways (using an identifier or a string), there is no error.
如果您以任何一种描述的方式（使用标识符或字符串）手动写出查询，则不会出现错误。
SELECT TOP 1 *
FROM MyTable
ORDER BY MyField DESC
;
SELECT TOP 1 *
FROM MyTable
ORDER BY "MyField" DESC
;
SELECT TOP 1 *
FROM MyTable
ORDER BY 'MyField' DESC
;
So if you perform a CAST()on that same parameter, its value is converted to a string, and the query executes successfully:
因此，如果您CAST()对同一参数执行 a ，其值将转换为字符串，并且查询将成功执行：
DECLARE @ORDERBY AS NVARCHAR(20)
;
SELECT @ORDERBY = :Param1 --(Supposing that the user enters the text 'MyField')
;
SELECT TOP 1 *
FROM MyTable
ORDER BY CAST(@ORDERBY AS NVARCHAR(20)) DESC
;
In this instance, (again, supposing that the user wrote the string 'MyField' as the value of :Param1), the actual query being executed is:
在这个例子中，（同样，假设用户将字符串 'MyField' 作为 :Param1 的值），实际执行的查询是：
SELECT TOP 1 *
FROM MyTable
ORDER BY 'MyField' DESC
;
This query executes successfully, with no errors and no apparent, significant impact on performance, without the need to enumerate all of the possible user inputs into a CASEstatement that could, potentially, stretch out to hundreds of possible values.
此查询成功执行，没有错误，对性能没有明显的显着影响，无需将所有可能的用户输入枚举到CASE可能扩展到数百个可能值的语句中。
I have used this solution many times in Microsoft SQL Server, from 2005 up to 2016, with no problems whatsoever.
从 2005 年到 2016 年，我在 Microsoft SQL Server 中多次使用此解决方案，没有任何问题。
Hopefully this can still be helpful to someone.
希望这仍然可以对某人有所帮助。
