“订购者"使用列名参数

2021-12-29 00:00:00 visual-studio sql parameters sql-server sql-order-by

我们希望在使用 Visual Studio 数据集设计器创建的查询或存储过程的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.

示例:

  FROM TableName
 WHERE (Forename LIKE '%' + @SearchValue + '%') OR
       (Surname LIKE '%' + @SearchValue + '%') OR
       (@SearchValue = 'ALL')
ORDER BY @OrderByColumn

显示此错误:

Variables are only allowed when ordering by an expression referencing 
a column name.

推荐答案

你应该能够做这样的事情:

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;

  • 将 1 分配给 @OrderByColumn 以对 Forename 进行排序.
  • 分配 2 以对 Surname 进行排序.
  • 等等……您可以将此方案扩展到任意数量的列.

    • Assign 1 to @OrderByColumn to sort on Forename.
    • Assign 2 to sort on Surname.
    • Etc... you can expand this scheme to arbitrary number of columns.

      • 不过要注意性能.这些类型的构造可能会干扰查询优化器找到最佳执行计划的能力.例如，即使 Forename 被索引覆盖，查询可能仍然需要完整排序，而不是仅仅按顺序遍历索引.

      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.

      如果是这种情况，并且您无法忍受性能影响，则可能需要为每个可能的排序顺序使用单独的查询版本，从而使客户端的事情变得相当复杂.

      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.

相关文章