我们希望在使用Visual Studio DataSet设计器创建的查询或存储过程的"Order By“子句中使用参数。
示例:
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.
发布于 2012-12-13 02:29:20
您应该能够这样做:
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;
@OrderByColumn
to sort on Forename
.Surname
.不过,要注意性能。这些类型的构造可能会干扰查询优化器找到最佳执行计划的能力。例如,即使索引涵盖了Forename
,查询可能仍然需要完整的排序,而不仅仅是按顺序遍历索引。
如果是这种情况,并且您不能忍受性能影响,那么可能有必要为每个可能的排序顺序使用单独的查询版本,这会使客户端的事情变得相当复杂。
发布于 2019-03-27 19:50:39
我知道我在这个帖子上来得太晚了,但我只是想把这篇文章贴出来,以防其他人有类似的问题。
当您尝试直接对参数执行ORDER BY
时,似乎会出现此问题,因为SQL Server要求您提供一个数字(1表示第一个字段,2表示第二个字段,依此类推...),或者提供一个列名,该列名表示为标识符(MyField或"MyField")或字符串('MyField')。
例如:
DECLARE @ORDERBY AS NVARCHAR(20)
;
SELECT @ORDERBY = :Param1 --(Supposing that the user enters 'MyField')
;
SELECT TOP 1 *
FROM MyTable
ORDER BY @ORDERBY DESC
;
您会得到以下错误:
由ORDER by数字1标识的选择项包含一个变量,该变量是标识列位置的表达式的一部分。仅当按引用列名的表达式排序时,才允许使用变量。(SQLSTATE=42000) (1008) (Severity=16)
如果您以上述任何一种方式(使用标识符或字符串)手动编写查询,则不会出现错误。
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
;
因此,如果您对同一参数执行CAST()
,则其值将被转换为字符串,并且查询将成功执行:
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
;
在本例中(同样,假设用户将字符串'MyField‘写为:param1的值),实际执行的查询是:
SELECT TOP 1 *
FROM MyTable
ORDER BY 'MyField' DESC
;
此查询成功执行,没有错误,也不会对性能产生明显的重大影响,无需将所有可能的用户输入枚举到CASE
语句中,该语句可能会扩展到数百个可能的值。
从2005年到2016年,我在Microsoft SQL Server中多次使用此解决方案,没有任何问题。
希望这对某些人仍然有帮助。
https://stackoverflow.com/questions/13844678
复制相似问题