Help with CASE statement

I need some help with a case statement that I'm trying to write. I wrote thi
s
query a couple of ws ago, but then my sql server had a hardware failure
and I lost all of my stored procs and now I can't remember how I implemented
it. I'm trying to write a dynamic "IN" statement using a case statement, but
I can't get the syntax quite right. Here's an example of what I'm trying to
do (using different data). I realize this could be simplified using a decode
table, but that isn't an option in this case. My problem is how I'm handling
the part of the statement after each "THEN" statement (I might have used a
Convert statement, but I don't remember). I think it's reading my values as
one long string (i.e.-'Apples, Oranges, Bananas) instead of separate values
(i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
SELECT column1
FROM tblMyTable
WHERE column2 IN(
CASE @.inputVariable
WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
END
)You cannot use in like that..
try this
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'fruit' and
column2 IN( 'Apples' ,'Oranges' ,'Bananas'))
or
(@.inputVariable = 'Vegetable' and column2 IN('Corn' ,'Green Beans'))
Hope this helps.
"Dustin" wrote:

> I need some help with a case statement that I'm trying to write. I wrote t
his
> query a couple of ws ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I implement
ed
> it. I'm trying to write a dynamic "IN" statement using a case statement, b
ut
> I can't get the syntax quite right. Here's an example of what I'm trying t
o
> do (using different data). I realize this could be simplified using a deco
de
> table, but that isn't an option in this case. My problem is how I'm handli
ng
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values a
s
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate value
s
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||Hi, Justin
The best way would be to use a table for this, but since you say that
it's not an option, try something like this:
SELECT column1
FROM tblMyTable
WHERE @.inputVariable='Fruit' AND column2 IN
('Apples','Oranges','Bananas')
OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
Razvan|||Dustin,
CASE is an expression (not a statement) that returns a scalar value. It
cannot return a set of values. In your code the CASE expression generates a
single character string value made of the concatenated elements. e.g., when
@.inputVariable = 'Fruit', you logically get:
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples, Oranges, Bananas');
Which is logically equivalent to:
SELECT column1
FROM tblMyTable
WHERE column2 = 'Apples, Oranges, Bananas';
Which probably isn't what you're after.
Here are a couple of options (not tested); I suspect the former will perform
better:
IF @.inputVariable = 'Fruit'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples', 'Oranges', 'Bananas');
ELSE IF @.inputVariable = 'Vegetable'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Corn', 'Green Beans');
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Dustin" <Dustin@.discussions.microsoft.com> wrote in message
news:475EA188-4D13-4613-8478-E9C268572184@.microsoft.com...
>I need some help with a case statement that I'm trying to write. I wrote
>this
> query a couple of ws ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I
> implemented
> it. I'm trying to write a dynamic "IN" statement using a case statement,
> but
> I can't get the syntax quite right. Here's an example of what I'm trying
> to
> do (using different data). I realize this could be simplified using a
> decode
> table, but that isn't an option in this case. My problem is how I'm
> handling
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values
> as
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate
> values
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||of course with the braces in place ;)
--
"Razvan Socol" wrote:

> Hi, Justin
> The best way would be to use a table for this, but since you say that
> it's not an option, try something like this:
> SELECT column1
> FROM tblMyTable
> WHERE @.inputVariable='Fruit' AND column2 IN
> ('Apples','Oranges','Bananas')
> OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
> Razvan
>|||I believe it will work fine without the extra parenthesis, but the
parenthesis will make it easier to read and leave no question as to what is
intended.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
> of course with the braces in place ;)
> --
>
>
> "Razvan Socol" wrote:
>|||boy.. thats a news to me.. Always thought AND and OR had the same precedence
.
thanks for pointing that out. Though I will never use it without braces :)
--
"Jim Underwood" wrote:

> I believe it will work fine without the extra parenthesis, but the
> parenthesis will make it easier to read and leave no question as to what i
s
> intended.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
>
>|||I never use it without parenthesis either. It just gets too confusing, and
too easy to get the wrong results. That and I still forget sometimes that
AND has a higher precedence than OR, so the parens protect me from myself.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:7EAE1F57-00B1-4002-8B32-E6BCC8DDCB84@.microsoft.com...
> boy.. thats a news to me.. Always thought AND and OR had the same
precedence..
> thanks for pointing that out. Though I will never use it without braces :)
> --
>
>
> "Jim Underwood" wrote:
>
is
that|||Thank you all for your help. I was able to solve the problem using the
following suggested syntax:
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));