T sql Case in Where clause

Question

I know this has been asked a few times but I can't seem to get this sorted. What am I doing wrong here?

WHERE B.ACCTSET IN CASE 
        WHEN @AcctSet ='Maize' THEN ('001X04','010X04')
        WHEN @AcctSet ='Wheat' THEN ('002X04')
        ELSE ('004X04')
        END

Help will be really appreciated.

One old answer is [here](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297). — HABO, Jan 17 '20 at 15:36

score 0 · Answer 1 · answered Jan 17 '20 at 15:20

A CASE statement can only return one value, so the line for 'Maize' will not work.

The CASE statement would also be within the parenthesis.

WHERE B.ACCTSET IN (CASE 
        WHEN @AcctSet ='Maize' THEN '001X04'
        WHEN @AcctSet ='Wheat' THEN '002X04'
        ELSE '004X04'
        END
       )

If the results are fixed like in your example then you could create a table var and insert the records in to it based on your logic, then join on that or use it in your IN().

DECLARE @Values TABLE( [Value] VARCHAR(6))

IF @AcctSet ='Maize'
BEGIN
   INSERT INTO @Values VALUE('001X04')
   INSERT INTO @Values VALUE('010X04')
END
ELSE IF @AcctSet ='Wheat'
BEGIN
   INSERT INTO @Values VALUE('002X04')
END
ELSE
BEGIN
   INSERT INTO @Values VALUE('004X04')
END

/..snip../
WHERE WHERE B.ACCTSET IN (SELECT [Value] FROM @Values)

score 0 · Answer 2 · answered Jan 17 '20 at 15:29

Your CASE expression should return only 1 value and not a list of values like this:

WHERE @AcctSet = CASE 
  WHEN B.ACCTSET IN ('001X04','010X04') THEN 'Maize'
  WHEN B.ACCTSET IN ('002X04') THEN 'Wheat'
  WHEN B.ACCTSET IN ('004X04') THEN @AcctSet
END

T sql Case in Where clause

2 Answers2