Assign table values to multiple variables using a single SELECT statement and CASE?
- by Darth Continent
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?
