“订购者"使用参数作为列名 [英] "Order By" using a parameter for the column name

问题描述

我们想在使用Visual Studio数据集设计器创建的查询或存储过程的订购依据"子句中使用参数.

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.

这篇关于“订购者"使用参数作为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！