SQL: CASE expression with declared variable

Question

I have following example that doesn't work in SQL server. table1.field1 is also a char(3)

DECLARE @CALCULATED_FIELD char(3);

SELECT table1.field1
       ,table1.field2
       ,CASE 
            WHEN table1.field2 = '1' THEN @CALCULATED_FIELD = table1.field1
            ELSE @CALCULATED_FIELD = 'ALL'
       END
FROM table1

Anyone knows the problem with the case statement?

Answer 1

The result of the CASE expression must be assigned to the variable, but it is not allowed in the same statement to return any columns from the table.
So this:

SELECT  
  @CALCULATED_FIELD = CASE 
    WHEN field2 = '1' THEN field1 
    ELSE 'ALL' 
  END 
FROM table1

is syntactically valid, although it will assign to the variable finally, the result of the CASE expression of the last row fetched.

By the name of the variable, I suspect that you want a 3d column which will be calculated by CASE:

SELECT field1, field2, 
  CASE 
    WHEN field2 = '1' THEN field1 
    ELSE 'ALL' 
  END calculated_field
FROM table1

See a simplified demo.