CASE STATEMENT

• rs

  SSCrazy

  Points: 2819

  More actions

  April 23, 2004 at 11:45 am

  #107772

  DECLARE @x VARCHAR(10)

  SET @x = 'SQL'

  SELECT * FROM TABLE1 WHERE COL1 = 'SOMETHING' AND

  COL2 CASE WHEN @x = 'SQL' THEN IN ('SQL1','SQL2') ELSE IN ('SQL3','SQL4') END

  But i am getting the following error

  Server: Msg 156, Level 15, State 1

  Incorrect syntax near the keyword 'IN'.

  What is wrong here, could you help me please.

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  April 23, 2004 at 12:19 pm

  #504054

  I've never used CASE in a WHERE (it can be done, but I'm not sure exactly how). But taking a stab, I think the CASE is in the wrong place:

  AND

  CASE WHEN @x = 'SQL' THEN

  COL2 IN ('SQL1','SQL2') ELSE

  COL2 IN ('SQL3','SQL4') END

  That's just a guess based on the requirement that first you have the comparison and THEN you have what you want to occur.

  So, first you compare @x to 'SQL' THEN depending on the result you want to compare COL2 to 'SQL1' or 'SQL2' or ELSE you want to compare COL2 to 'SQL3' or 'SQL4'.

  -SQLBill

• kg_murthy

  SSC Journeyman

  Points: 86

  More actions

  April 23, 2004 at 12:40 pm

  #504057

  Try this,

  The usage of case was wrong.  The case can return only one value.

  SELECT * FROM TABLE1 WHERE COL1 = 'SOMETHING' AND

  COL2 IN ( CASE WHEN @x = 'SQL' THEN 'SQL1'  ELSE 'SQL3' END ,

     CASE WHEN @x = 'SQL' THEN 'SQL2'  ELSE 'SQL4' END )

  Thanks,

  Murthy