Brent Weitzel Brent Weitzel - 6 months ago 10
SQL Question

Use a variable in a Case Statement

I am trying to get a result from a table dependent on a field and populate the variable from different fields dependent on one of the fields. Here is what I have

DECLARE @MyText Text;
SELECT
bu4contart,
CASE WHEN bu4contart IN('40HR', '40RF') THEN
'Temp: ' + bu4setpoint
ELSE
'Oversize: ' + bu4ohtop
END AS @MyText
FROM MyTable

Answer

Your problem is that you want a scalar value from a (possibly) set of rows. This could work for a scalar situation.

DECLARE  @MyText Text = 
(SELECT Top 1

CASE WHEN bu4contart IN('40HR', '40RF') THEN
     'Temp: ' + bu4setpoint
ELSE 
     'Oversize: ' + bu4ohtop
END AS MyText
FROM MyTable
Order By FieldThatOrdersForTop1FromMultipleRowsIfNecessary
)