如何正确执行CASE逻辑？创建计算列时遇WHEN语法错误求助

Hey there! Let's break down why you're running into this error and how to fix it. The most common culprit here is mixing up conditional logic syntax for calculated columns in SQL—we don't use if...else directly in column expressions; instead, we rely on the CASE statement, which is designed for returning conditional values in queries and calculated columns.

Common Mistake Example #

If you tried something like this (using if...else in the calculated column definition), that's exactly why you're seeing the error:

-- ❌ Wrong approach - IF is a flow control statement, not an expression
ALTER TABLE YourTable
ADD YourCalculatedColumn AS IF (YourColumn > 50) THEN 'Above Average' ELSE 'Below Average'

Correct Approach: Use the CASE Statement #

CASE is the right tool here—it acts as an expression that returns a value based on conditions. There are two main flavors you can use:

1. Search CASE (for complex conditions)

Use this when you need to evaluate different boolean conditions (like ranges, comparisons, etc.):

-- ✅ Correct calculated column with search CASE
ALTER TABLE YourTable
ADD PerformanceRating AS
    CASE
        WHEN Score >= 90 THEN 'Excellent'
        WHEN Score >= 70 AND Score < 90 THEN 'Good'
        WHEN Score >= 50 AND Score < 70 THEN 'Needs Improvement'
        ELSE 'Poor'
    END

2. Simple CASE (for equality checks)

Use this when you're matching a column's value directly against specific values:

-- ✅ Correct calculated column with simple CASE
ALTER TABLE Orders
ADD OrderStatus AS
    CASE OrderStatusCode
        WHEN 'SH' THEN 'Shipped'
        WHEN 'PR' THEN 'Processing'
        WHEN 'CN' THEN 'Cancelled'
        ELSE 'Pending'
    END

Key Notes to Avoid Future Errors #

• Always make sure your CASE statement is properly structured: each WHEN is followed by a condition and THEN value, and wrap the whole block in CASE/END.
• While ELSE is optional, it's a good practice to include it—without it, rows that don't match any WHEN condition will return NULL.
• Ensure all values returned by THEN/ELSE are of the same (or compatible) data type. For example, don't return a string in one THEN and a number in another.

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