I have a table user_config that stores code names and values.

For example, something like this:

declare
    @country varchar(2),
    @currency varchar(3),
    @alternate_currencies varchar(100);

select @country = u.value
from user_config u
where u.code = 'ctr'
  and u.id = 'sam';

select @currency = u.value
from user_config u
where u.code = 'curr'
  and u.id = 'sam';

select @alternate_currencies = u.value
from user_config u
where u.code = 'altcurr'
  and u.id = 'sam';

Is there a way to assign variables in one SQL, depending on the value of code?

CodePudding user response：

The CASE expressions will help you here

select @country = (case when u.code = 'ctr' then u.value else @country end),
       @currency = (case when u.code = 'curr' then u.value else @currency end),
       @alternate_currencies = (case when u.code = 'altcurr' then u.value else @alternate_currencies end)
  from user_config u
 where u.id = 'sam';

dbfiddle

CodePudding user response：

The answer given by @ekochergin has undefined behavior, because it is using variable coalescing to aggregate. Instead, use normal conditional aggregation, and then assign to variables

select @country = MIN(case when u.code = 'ctr' then u.value end),
       @currency = MIN(case when u.code = 'curr' then u.value end),
       @alternate_currencies = MIN(case when u.code = 'altcurr' then u.value end)
  from user_config u
 where u.id = 'sam';