SQL Server查询报错：ORDER BY列表第1位遇到常量表达式

报错信息 #

A constant expression was encountered in the ORDER BY list, position 1

原查询代码 #

SET DATEFORMAT DMY
SET NOCOUNT ON

DECLARE @TotalRows INT

SELECT
    [Failure].[FailureId] AS [FailureId],
    [Failure].[HTTPCode] AS [HTTPCode],
    [Failure].[EmergencyLevel] AS [EmergencyLevel],
    [Failure].[Message] AS [Message],
    [Failure].[StackTrace] AS [StackTrace],
    [Failure].[Source] AS [Source],
    [Failure].[Comment] AS [Comment],
    [Failure].[Active] AS [Active],
    [Failure].[UserCreationId] AS [UserCreationId],
    [Failure].[UserLastModificationId] AS [UserLastModificationId],
    [Failure].[DateTimeCreation] AS [DateTimeCreation],
    [Failure].[DateTimeLastModification] AS [DateTimeLastModification]
FROM 
    [Failure]
WHERE
    1 = 1
    AND ('' = '' OR ([Failure].[FailureId] LIKE  '%' + '' + '%'))
ORDER BY
    CASE WHEN ('FailureId' = 'FailureId' AND 1 = 0) THEN [FailureId] END ASC,
    CASE WHEN ('FailureId' = 'FailureId' AND 1 = 1) THEN [FailureId] END DESC,
    CASE WHEN ('FailureId' = 'HTTPCode' AND 1 = 0) THEN [HTTPCode] END ASC,
    CASE WHEN ('FailureId' = 'HTTPCode' AND 1 = 1) THEN [HTTPCode] END DESC,
    CASE WHEN ('FailureId' = 'EmergencyLevel' AND 1 = 0) THEN [EmergencyLevel] END ASC,
    CASE WHEN ('FailureId' = 'EmergencyLevel' AND 1 = 1) THEN [EmergencyLevel] END DESC,
    CASE WHEN ('FailureId' = 'Message' AND 1 = 0) THEN [Message] END ASC,
    CASE WHEN ('FailureId' = 'Message' AND 1 = 1) THEN [Message] END DESC,
    CASE WHEN ('FailureId' = 'StackTrace' AND 1 = 0) THEN [StackTrace] END ASC,
    CASE WHEN ('FailureId' = 'StackTrace' AND 1 = 1) THEN [StackTrace] END DESC,
    CASE WHEN ('FailureId' = 'Source' AND 1 = 0) THEN [Source] END ASC,
    CASE WHEN ('FailureId' = 'Source' AND 1 = 1) THEN [Source] END DESC,
    CASE WHEN ('FailureId' = 'Comment' AND 1 = 0) THEN [Comment] END ASC,
    CASE WHEN ('FailureId' = 'Comment' AND 1 = 1) THEN [Comment] END DESC,
    CASE WHEN ('FailureId' = 'Active' AND 1 = 0) THEN [Active] END ASC,
    CASE WHEN ('FailureId' = 'Active' AND 1 = 1) THEN [Active] END DESC,
    CASE WHEN ('FailureId' = 'UserCreationId' AND 1 = 0) THEN [UserCreationId] END ASC,
    CASE WHEN ('FailureId' = 'UserCreationId' AND 1 = 1) THEN [UserCreationId] END DESC,
    CASE WHEN ('FailureId' = 'UserLastModificationId' AND 1 = 0) THEN [UserLastModificationId] END ASC,
    CASE WHEN ('FailureId' = 'UserLastModificationId' AND 1 = 1) THEN [UserLastModificationId] END DESC,
    CASE WHEN ('FailureId' = 'DateTimeCreation' AND 1 = 0) THEN [DateTimeCreation] END ASC,
    CASE WHEN ('FailureId' = 'DateTimeCreation' AND 1 = 1) THEN [DateTimeCreation] END DESC,
    CASE WHEN ('FailureId' = 'DateTimeLastModification' AND 1 = 0) THEN [DateTimeLastModification] END ASC,
    CASE WHEN ('FailureId' = 'DateTimeLastModification' AND 1 = 1) THEN [DateTimeLastModification] END DESC
OFFSET (1 - 1) * 2 ROWS
FETCH NEXT 2 ROWS ONLY

报错原因 #

• 你的ORDER BY子句中存在大量恒假/恒真的常量判断，比如('FailureId' = 'FailureId' AND 1 = 0)，其中1=0永远为假，导致对应的CASE表达式始终返回NULL（因为没有ELSE分支）。
• SQL Server不允许ORDER BY列表中出现常量表达式或始终返回相同值的表达式，这类表达式无法提供有效的排序逻辑，同时OFFSET/FETCH要求排序必须稳定，不能依赖无意义的常量值排序。
• 第一个CASE表达式就是典型的常量表达式，这直接触发了报错。

解决办法 #

需要重构排序逻辑，只保留实际有效的排序分支，或者用变量动态控制排序字段和方向，避免无意义的常量判断。

方案1：动态变量控制排序 #

SET DATEFORMAT DMY
SET NOCOUNT ON

DECLARE @SortColumn NVARCHAR(50) = 'FailureId' -- 指定排序字段
DECLARE @IsDesc BIT = 1 -- 1=降序，0=升序

SELECT
    [Failure].[FailureId] AS [FailureId],
    [Failure].[HTTPCode] AS [HTTPCode],
    [Failure].[EmergencyLevel] AS [EmergencyLevel],
    [Failure].[Message] AS [Message],
    [Failure].[StackTrace] AS [StackTrace],
    [Failure].[Source] AS [Source],
    [Failure].[Comment] AS [Comment],
    [Failure].[Active] AS [Active],
    [Failure].[UserCreationId] AS [UserCreationId],
    [Failure].[UserLastModificationId] AS [UserLastModificationId],
    [Failure].[DateTimeCreation] AS [DateTimeCreation],
    [Failure].[DateTimeLastModification] AS [DateTimeLastModification]
FROM 
    [Failure]
WHERE
    1 = 1
    AND ('' = '' OR ([Failure].[FailureId] LIKE  '%' + '' + '%'))
ORDER BY
    -- 仅保留当前需要的排序分支
    CASE WHEN @SortColumn = 'FailureId' AND @IsDesc = 0 THEN [FailureId] END ASC,
    CASE WHEN @SortColumn = 'FailureId' AND @IsDesc = 1 THEN [FailureId] END DESC,
    CASE WHEN @SortColumn = 'HTTPCode' AND @IsDesc = 0 THEN [HTTPCode] END ASC,
    CASE WHEN @SortColumn = 'HTTPCode' AND @IsDesc = 1 THEN [HTTPCode] END DESC,
    -- 其他字段按需添加
    -- 最后加默认排序保证稳定性
    [FailureId] ASC
OFFSET (1 - 1) * 2 ROWS
FETCH NEXT 2 ROWS ONLY

方案2：简化排序逻辑（同字段统一处理） #

SET DATEFORMAT DMY
SET NOCOUNT ON

DECLARE @SortColumn NVARCHAR(50) = 'FailureId'
DECLARE @IsDesc BIT = 1

SELECT
    [Failure].[FailureId] AS [FailureId],
    [Failure].[HTTPCode] AS [HTTPCode],
    [Failure].[EmergencyLevel] AS [EmergencyLevel],
    [Failure].[Message] AS [Message],
    [Failure].[StackTrace] AS [StackTrace],
    [Failure].[Source] AS [Source],
    [Failure].[Comment] AS [Comment],
    [Failure].[Active] AS [Active],
    [Failure].[UserCreationId] AS [UserCreationId],
    [Failure].[UserLastModificationId] AS [UserLastModificationId],
    [Failure].[DateTimeCreation] AS [DateTimeCreation],
    [Failure].[DateTimeLastModification] AS [DateTimeLastModification]
FROM 
    [Failure]
WHERE
    1 = 1
    AND ('' = '' OR ([Failure].[FailureId] LIKE  '%' + '' + '%'))
ORDER BY
    CASE @SortColumn
        WHEN 'FailureId' THEN [FailureId]
        WHEN 'HTTPCode' THEN CAST([HTTPCode] AS SQL_VARIANT)
        WHEN 'EmergencyLevel' THEN CAST([EmergencyLevel] AS SQL_VARIANT)
        -- 不同类型字段需转成相同类型（如SQL_VARIANT）避免类型冲突
    END
    CASE WHEN @IsDesc = 1 THEN DESC ELSE ASC END,
    [FailureId] ASC -- 默认排序保证稳定性
OFFSET (1 - 1) * 2 ROWS
FETCH NEXT 2 ROWS ONLY

内容的提问来源于stack exchange，提问作者Matías Alejandro Novillo