T-SQL报错：关键字'case'附近有语法错误，求排查代码问题

嘿，我一眼就揪出你代码里的问题啦！这个报错主要是因为你的CASE表达式写法不符合T-SQL的语法规范，同时列返回的逻辑也搞错了。咱们一步步来修正：

原代码的核心问题 #

• 开头的SELECT @ifAtOffice CASE完全多余还违反语法——CASE表达式不需要前置变量，直接用来计算列值就行。
• 你试图在一个CASE分支里同时返回description和resultCode两个列，但T-SQL的CASE是标量表达式，只能生成单个值，没法同时定义多个列。

修正后的代码 #

根据你的业务逻辑，正确的写法应该是为每个要返回的列单独编写CASE逻辑：

SELECT 
    CASE 
        WHEN @ifAtOffice = 1 THEN 'Device is still assigned to a client, locate is at Office'
        WHEN @ifAtOffice = 0 THEN 'Device is still assigned to a client, locate is not at Office'
    END AS description,
    CASE 
        WHEN @ifAtOffice = 1 THEN 2
        WHEN @ifAtOffice = 0 THEN 3
    END AS resultCode

额外优化（可选） #

如果@ifAtOffice可能为NULL或者其他非0/1的值，建议加上ELSE分支处理边界情况，避免返回NULL：

SELECT 
    CASE 
        WHEN @ifAtOffice = 1 THEN 'Device is still assigned to a client, locate is at Office'
        WHEN @ifAtOffice = 0 THEN 'Device is still assigned to a client, locate is not at Office'
        ELSE 'Unknown device location status'
    END AS description,
    CASE 
        WHEN @ifAtOffice = 1 THEN 2
        WHEN @ifAtOffice = 0 THEN 3
        ELSE -1 -- 用特定代码标记未知状态
    END AS resultCode

这样调整后，语法错误就会消失，逻辑也更清晰健壮啦！

内容的提问来源于stack exchange，提问作者Jeff Winkler