CODEWITHSUNDEEP
sql-server-2008-r2
RobertKing
RobertKing

Reputation: 1921

sql get values inside variables conditionally

I'm trying to get values in variables based on condition as below,

DECLARE @HP INT,@PP INT;

SELECT CASE COLUMN_1 
         WHEN 'VAL1' THEN SELECT @HP = COLUMN_CD
         WHEN 'VAL2' THEN SELECT @PP = COLUMN_CD
    END
         FROM TBL

no success. how can i do this?

Note: here COLUMN_1 is UNIQUE

Upvotes: 0

Views: 13

Answers (1)

Pred
Pred

Reputation: 9042

TheCASE..WHEN structure should return with a scalar value, not with a resultset. SELECT statement returns a resultset. (The SELECT @HP = COLUMN_CD parts)

You have to assign the values to the variables individually like this:

DECLARE @HP INT,@PP INT;

SELECT
    @HP = CASE WHEN COLUMN_1 = 'VAL1' THEN COLUMN_CD ELSE @HP END
  , @PP = CASE WHEN COLUMN_1 = 'VAL2' THEN COLUMN_CD ELSE @PP END
FROM
  TBL

OR if you do not want to preserve the original value of the variables, you can replace the @HP and @PP in the ELSE part to NULL:

DECLARE @HP INT,@PP INT;

SELECT
    @HP = CASE WHEN COLUMN_1 = 'VAL1' THEN COLUMN_CD ELSE NULL END
  , @PP = CASE WHEN COLUMN_1 = 'VAL2' THEN COLUMN_CD ELSE NULL END
FROM
  TBL

Upvotes: 1

Related Questions