How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly becaus
e
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, i
t
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired result
s.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:

>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis