CODEWITHSUNDEEP
sql-servert-sqlvariablescase
Hrabal
Hrabal

Reputation: 2525

TSQL variable assignment in the "then" of select case when

I'm trying to SET a variable with other variables using a case when condition that comes from a select:

DECLARE @var_to_be_set INT
DECLARE @var1 INT
DECLARE @var2 INT

SELECT
  CASE WHEN col = 1 THEN @var_to_be_set = col + @var1
       WHEN col = 2 THEN @var_to_be_set = col + @var2
  END
FROM table [etc..etc..]

Is this possible? I keep having Incorrect syntax errors in every way I try. Tried a solution on google, but can't find anything relevant.

Thanks

Upvotes: 3

Views: 348

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Yes you can assign value to variable with CASE:

DECLARE @var_to_be_set INT
DECLARE @var1 INT               -- intialize variables with values
DECLARE @var2 INT

SELECT @var_to_be_set = 
  CASE WHEN col = 1 THEN col + @var1
       WHEN col = 2 THEN col + @var2
  END
FROM table [etc..etc..]

LiveDemo

Keep in mind that if your query returns multiple rows, you will get only one value.

Shorter version:

SELECT @var_to_be_set = col + CASE col 
                              WHEN 1 THEN @var1 
                              WHEN 2 THEN @var2 
                              END
...

or even (SQL Server 2012+):

SELECT @var_to_be_set = col + CHOOSE(col, @var1, @var2)
...

Upvotes: 4

Related Questions