CASE Statement

[229257]

Hi!

I'm pretty new to tsql programming and was wondering if the following is possible with a CASE statement.

Depending on the parameters sent to a SP I want to retrieve different product codes. The tidiest way to do would be have a statement like:-
WHERE [Product].Type IN
CASE
WHEN @ExcInvDeposit = 1 THEN (124,344,549)
WHEN @ExcInvDeposit = 0 THEN (124,344)
END

Unfortunately this doesnt work and gives a syntax error. Does anyone know how i can combine a set of IN values with a CASE statement?

I have done various testing and following works:-
WHERE [Product].Type =
CASE
WHEN @ExcInvDeposit = 1 THEN 549
WHEN @ExcInvDeposit = 0 THEN 124
END
This suggests it is combining the IN clause that is causing the problem.

Any help would be much appreciated,

Thanks
229257

 

[gmmastros]

Try this...

WHERE  ((@ExcInvDeposit = 1 And [Product].Type IN (124,344,549))
       or (@ExcInvDeposit = 0 And [Product].Type IN (124,344,549)))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry... I posted too early.

WHERE  ((@ExcInvDeposit = 1 And [Product].Type IN (124,344,549))
       or (@ExcInvDeposit = 0 And [Product].Type IN (124,344)))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

(124,344,549) is a multi-valued expression. It can be an operand to the IN operator, and it is logically the same as a rowset-returning query as in:

WHERE a IN (SELECT a FROM x WHERE z=2)

The CASE statement works with single, scalar values. It cannot accept multi-valued expressions.

Just like you can't do

SELECT CASE WHEN 1 = 1 THEN (SELECT a FROM x) END

you can't do

SELECT CASE WHEN 1 = 1 THEN (1, 2, 3) END

What you were trying to do with your query was skip over the case statement and inject the multi-valued expression into the IN statement preceding the CASE. This sort of jumping around is not possible. CASE evaluates to a scalar expression: a single value. Period. It can't return a multivalued expression because that's simply not how it works.

IN must always be followed by, inside of parentheses, either a list of scalars separated by commas (thus creating a multivalued expression) or a rowset-returning SELECT statement.

Another similar thing that people try to do is this:

SET @z = '1, 2, 3, 4'
SELECT * FROM x WHERE a IN (@z)

But this expands to:

SELECT * FROM x WHERE a IN ('1, 2, 3, 4')

which is VERY different from the intended

SELECT * FROM x WHERE a IN (1, 2, 3, 4)

Do you see? The first one is equivalent to:

SELECT * FROM x WHERE a = '1, 2, 3, 4'

Or perhaps another way to make it clear is this:

SELECT * FROM x WHERE a IN ('1, 2, 3, 4', 'frog', '3 & 7', '18, 42, and 65')
-->
SET @z = '1, 2, 3, 4'
SET @y = 'frog'
SET @x = '3' & 7'
SET @w = '18, 42, and 65'
SELECT * FROM x WHERE a IN (@z)
SELECT * FROM x WHERE a IN (@z, @y, @x, @w)

So you see that it's looking for the single literal scalar value '1, 2, 3, 4' and not individual values '1', '2', '3', and '4' (or 1, 2, 3, and 4)?

This attempted construction is confusing what the parser intends to do with the expressions given it. It has no facility for taking random string variables and popping their contents into the actual SQL expression. And even if it did, how would it know in this case that you wanted that and weren't instead looking for the actual string '1, 2, 3, 4'?

I mention all this because it may help you when you're thinking about what wasn't working in the original WHERE clause you posted. CASE statements return single values, and the parser can't know that you want to shift around various levels of meaning or accomplish that even if it knew.

Since IN () gets expanded to an OR clause anyway, you also have another, probably simpler option:

WHERE
   Product.Type = 124
   OR Product.Type = 344
   OR (
      @ExcInvDeposit = 1
      AND Product.Type = 549
   )

If you want to read more about the different kinds of statements in SQL Server, check out Mixed-Up Statement Types. I wrote this to try to help clear up for people some common stumbling blocks seen in these forums.