Assign table values to multiple variables using a single SELECT statement and CASE?

Question

I'm trying to assign values contained in a lookup table to multiple variables by using a single SELECT having multiple CASE statements.

The table is a lookup table with two columns like so:

[GreekAlphabetastic]

  SystemID    Descriptor
  --------    ----------
  1           Alpha
  2           Beta
  3           Epsilon

This is my syntax:

SELECT 

    @VariableTheFirst = 
        CASE
            WHEN myField = 'Alpha' THEN tbl.SystemID
        END,

    @VariableTheSecond = 
        CASE
            WHEN myField = 'Beta' THEN tbl.SystemID
        END,

    @VariableTheThird = 
        CASE
            WHEN myField = 'Epsilon' THEN tbl.SystemID
        END

FROM GreekAlphabetastic tbl

However, when I check the variables after this statement executes, I expected each to be assigned the appropriate value, but instead only the last has a value assigned.

SELECT 
    @VariableTheFirst AS First, 
    @VariableTheSecond AS Second, 
    @VariableTheThird AS Third

Results:

    First    Second    Third
    NULL     NULL      3

What am I doing wrong?

Answer 1

when making assignments to local variables from a SELECT, only the last row processed will affect the variables. for the third row, the CASE myField = 'Alpha' and CASE myField = 'Beta' are false and the variables are set to NULL. The CASE myField = 'Epsilon' is true and @VariableTheThird is assigned 3.

if you want this to work do this:

SELECT @VariableTheFirst = tbl.SystemID WHERE myField = 'Alpha'
SELECT @VariableTheSecond = tbl.SystemID WHERE myField = 'Beta'
SELECT @VariableTheThird = tbl.SystemID WHERE myField = 'Epsilon'

Answer 2

The first 2 variables are being reset to null after being assigned. i.e. when it hits the Epsilon record, the first 2 variables are being assigned to null as there is nothing to prevent that in the CASE statement.

So, try this:

SELECT     
    @VariableTheFirst = 
        CASE
            WHEN Descriptor = 'Alpha' THEN tbl.SystemID
            ELSE @VariableTheFirst
        END,

    @VariableTheSecond = 
        CASE
            WHEN Descriptor = 'Beta' THEN tbl.SystemID
            ELSE @VariableTheSecond
        END,

    @VariableTheThird = 
        CASE
            WHEN Descriptor = 'Epsilon' THEN tbl.SystemID
            ELSE @VariableTheThird
        END
FROM GreekAlphabetastic tbl

Answer 3

SELECT     
        CASE
            WHEN Descriptor = 'Alpha' THEN @VariableTheFirst = isnull(@VariableTheFirst,tbl.SystemID)
            WHEN Descriptor = 'Beta' THEN @VariableTheSecond = isnull(@VariableTheSecond,tbl.SystemID)
            WHEN Descriptor = 'Epsilon' THEN @VariableTheThird = isnull(@VariableTheThird,tbl.SystemID)
        END
FROM GreekAlphabetastic tbl