“Order By”使用列名称的参数

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 DataSet Designer创建的查询或存储过程的“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.

1 个解决方案

#1

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 @OrderByColumn to sort on Forename.
• 将1分配给@OrderByColumn以对Forename进行排序。
• Assign 2 to sort on Surname.
• 分配2以对姓氏进行排序。
• Etc... you can expand this scheme to arbitrary number of columns.
• 等等...您可以将此方案扩展为任意数量的列。

---

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 Forename is 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.

如果是这种情况，并且您不能忍受性能影响，则可能需要为每个可能的排序顺序提供单独的查询版本，从而使客户端的内容变得复杂。