blocks|key|2838949|text|您应该能够这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2838950|SELECT+*
FROM
++++TableName
WHERE
++++(Forename+LIKE+'%25'+%2B+@SearchValue+%2B+'%25')+OR
++++(Surname+LIKE+'%25'+%2B+@SearchValue+%2B+'%25')+OR
++++(@SearchValue+=+'ALL')
ORDER+BY+
++++CASE+@OrderByColumn
++++WHEN+1+THEN+Forename
++++WHEN+2+THEN+Surname
++++END;|code-block|syntax|javascript|2838951|2838952|Assign+1+to+@OrderByColumn+to+sort+on+Forename.|unordered-list-item|offset|length|style|CODE|2838953|Assign+2+to+Surname.|2838954|Etc...排序您可以将此方案扩展到任意数量的列。|2838955|2838956|2838957|不过，要注意性能。这些类型的构造可能会干扰查询优化器找到最佳执行计划的能力。例如，即使索引涵盖了Forename，查询可能仍然需要完整的排序，而不仅仅是按顺序遍历索引。|2838958|如果是这种情况，并且您不能忍受性能影响，那么可能有必要为每个可能的排序顺序使用单独的查询版本，这会使客户端的事情变得相当复杂。|2838959|entityMap^0|0|0|0|C|E|12|8|0|C|7|0|0|0|0|1C|8|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|13|8|@$K|14|L|15|M|N]|$K|16|L|17|M|N]]|9|@]|A|$]]|$1|O|3|P|5|J|7|18|8|@$K|19|L|1A|M|N]]|9|@]|A|$]]|$1|Q|3|R|5|J|7|1B|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1E|8|@$K|1F|L|1G|M|N]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1H|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|Z|$]]

You should be able to do something like this:

<pre><code>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;
</code></pre>

<ul>
<li>Assign 1 to <code>@OrderByColumn</code> to sort on <code>Forename</code>.</li>
<li>Assign 2 to sort on <code>Surname</code>.</li>
<li>Etc... you can expand this scheme to arbitrary number of columns.</li>
</ul>

<hr>

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 <code>Forename</code> 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.

blocks|key|202267|text|我知道我在这个帖子上来得太晚了，但我只是想把这篇文章贴出来，以防其他人有类似的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|202268|当您尝试直接对参数执行ORDER+BY时，似乎会出现此问题，因为SQL+Server要求您提供一个数字(1表示第一个字段，2表示第二个字段，依此类推...)，或者提供一个列名，该列名表示为标识符(MyField或"MyField")或字符串('MyField')。|offset|length|style|CODE|202269|例如：|202270|DECLARE+@ORDERBY+AS+NVARCHAR(20)
;

SELECT+@ORDERBY+=+:Param1+--(Supposing+that+the+user+enters+'MyField')
;

SELECT+TOP+1+*
FROM+MyTable
ORDER+BY+@ORDERBY+DESC
;|code-block|syntax|javascript|202271|您会得到以下错误：|202272|202273|由ORDER+by数字1标识的选择项包含一个变量，该变量是标识列位置的表达式的一部分。仅当按引用列名的表达式排序时，才允许使用变量。(SQLSTATE=42000)+(1008)+(Severity=16)|blockquote|202274|202275|202276|如果您以上述任何一种方式(使用标识符或字符串)手动编写查询，则不会出现错误。|202277|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
;|202278|因此，如果您对同一参数执行CAST()，则其值将被转换为字符串，并且查询将成功执行：|202279|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
;|202280|在本例中(同样，假设用户将字符串'MyField‘写为:param1的值)，实际执行的查询是：|202281|SELECT+TOP+1+*
FROM+MyTable
ORDER+BY+'MyField'+DESC
;|202282|此查询成功执行，没有错误，也不会对性能产生明显的重大影响，无需将所有可能的用户输入枚举到CASE语句中，该语句可能会扩展到数百个可能的值。|202283|从2005年到2016年，我在Microsoft+SQL+Server中多次使用此解决方案，没有任何问题。|202284|希望这对某些人仍然有帮助。|202285|entityMap^0|0|B|8|0|0|0|0|0|0|0|0|0|0|D|6|0|0|0|0|18|4|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1H|8|@$D|1I|E|1J|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|1K|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1L|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|1O|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1R|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|L|7|1S|8|@]|9|@]|A|$M|N]]|$1|10|3|11|5|6|7|1T|8|@$D|1U|E|1V|F|G]]|9|@]|A|$]]|$1|12|3|13|5|L|7|1W|8|@]|9|@]|A|$M|N]]|$1|14|3|15|5|6|7|1X|8|@]|9|@]|A|$]]|$1|16|3|17|5|L|7|1Y|8|@]|9|@]|A|$M|N]]|$1|18|3|19|5|6|7|1Z|8|@$D|20|E|21|F|G]]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|22|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|23|8|@]|9|@]|A|$]]|$1|1E|3|-4|5|6|7|24|8|@]|9|@]|A|$]]]|1F|$]]

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 <code>ORDER BY</code> directly 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').

For example:

<pre><code>DECLARE @ORDERBY AS NVARCHAR(20)
;

SELECT @ORDERBY = :Param1 --(Supposing that the user enters 'MyField')
;

SELECT TOP 1 *
FROM MyTable
ORDER BY @ORDERBY DESC
;
</code></pre>

You get the following error:

<blockquote>
 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)
</blockquote>

If you write out the query manually in any one of the described ways (using an identifier or a string), there is no error.

<pre><code>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
;
</code></pre>

So if you perform a <code>CAST()</code> on that same parameter, its value is converted to a string, and the query executes successfully:

<pre><code>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
;
</code></pre>

In this instance, (again, supposing that the user wrote the string 'MyField' as the value of :Param1), the actual query being executed is:

<pre><code>SELECT TOP 1 *
FROM MyTable
ORDER BY 'MyField' DESC
;
</code></pre>

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 <code>CASE</code> statement that could, potentially, stretch out to hundreds of possible values.

I have used this solution many times in Microsoft SQL Server, from 2005 up to 2016, with no problems whatsoever.

Hopefully this can still be helpful to someone.

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.

Example:

<pre><code> FROM TableName
 WHERE (Forename LIKE '%' + @SearchValue + '%') OR
 (Surname LIKE '%' + @SearchValue + '%') OR
 (@SearchValue = 'ALL')
ORDER BY @OrderByColumn
</code></pre>

This error is displayed:

<pre><code>Variables are only allowed when ordering by an expression referencing 
a column name.
</code></pre>

"Order By" using a parameter for the column name

Visual Studio

SQL Server

我们希望在使用Visual Studio DataSet设计器创建的查询或存储过程的"Order By“子句中使用参数。示例：  FROM TableName WHERE (Forename LIKE '%' + @SearchValue + '%') OR       (Surname LIKE '%' + @Sea...

问"Order By“使用参数作为列名
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问"Order By“使用参数作为列名EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问"Order By“使用参数作为列名
EN