使用 CASE WHEN 的 ORDER BY 子句中的错误 [英] Error in ORDER BY clause using CASE WHEN

问题描述

我试图允许存储过程中的参数定义 ASC/DESC 排序顺序.

I'm trying to allow ASC/DESC sort order to be defined by a parameter in a stored procedure.

经过大量研究，我发现了这种方法(经过简化):

After lots of research, I found this approach (with simplification):

SELECT *
FROM MyTable
ORDER BY CASE WHEN @reverse = 1 THEN
              MyColumn
         END DESC,
         CASE WHEN @reverse = 0 THEN
              MyColumn
         END ASC

然而，这段代码抛出了以下错误:

However, this code throws the following error:

消息 408，级别 16，状态 1，第 8 行
在 ORDER BY 列表的位置 2 中遇到一个常量表达式.

Msg 408, Level 16, State 1, Line 8
A constant expression was encountered in the ORDER BY list, position 2.

为什么会这样?很明显，MyColumn 不是一个常量——它是一个列名.

Why is this happening? Clearly MyColumn isn't a constant - it is a column name.

在兼容模式 2016 中使用 SQL Server 2016 (130)

Using SQL Server 2016 in Compatibility Mode 2016 (130)

谢谢

推荐答案

一些搜索这条线帮助我了解更多..

按表达式排序必须计算为常量

ordering by an expression must evaluate to a constant

正如拉马克指出的那样，1=0 计算结果为 false 并且您没有定义 else 条件..所以 null 是未定义的，它会引发错误

so as Lamak pointed out,1=0 evaluates to false and you didn't define an else condition..so null is undefined and it throws error

摆脱像下面这样的尝试

ORDER BY CASE WHEN 1 = 1 THEN
              MyColumn
         END DESC,
         CASE WHEN 1 = 0 THEN
              col2 else col2--not your column,added this to make example clearer
         END ASC

还要注意，order by 中的表达式必须是唯一的，因此您的查询将不起作用(即使它成功)并抛出不同的错误，您也可以使用 ISNULL

also beware ,expressions in order by must be unique,so your query won't work(even if it succeeds) and throws different error,you can use ISNULL as well

这篇关于使用 CASE WHEN 的 ORDER BY 子句中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！