Case Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case Statement

Author

Topic

gqstud79
Starting Member

3 Posts

Posted - 2006-05-08 : 11:06:26

I need some help with a case statement that I'm trying to write. I wrote this query a couple of weeks 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). 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
)

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-08 : 11:12:32

Looks like you only need to change "IN" to "=":

SELECT column1
FROM tblMyTable
WHERE column2 =
CASE @inputVariable
WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
END

gqstud79
Starting Member

3 Posts

Posted - 2006-05-08 : 11:26:03

Thank you for your reply, but it still didn't seem to do the trick. My statement works fine when I only have one value after each "THEN" statement, but it doesn't return any records when I add a second value after it. I guess that's what makes me think I'm doing something wrong with the way I'm handling multiple values behind each "THEN" statement. I think it's treating the multiple values that I have as one long string - like 'Apples, Oranges, Bananas' rather than 'Apples', 'Oranges', 'Bananas'.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-08 : 12:31:08

Oh I see what you're trying to do. I think you're gonna have to use dynamic SQL for that:

DECLARE @inputVariable varchar(10), @str varchar(500)
SET @inputVariable = 'fruit'

SET @str =
'SELECT column1
FROM #tblMyTable
WHERE column2 IN(' +
CASE @inputVariable
WHEN 'Fruit' THEN '''apples'', ''oranges'', ''bananas'''
WHEN 'Vegetable' THEN '''corn'', ''green bean'''
END
+ ')'

EXEC (@str)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-08 : 13:12:20

Here are a couple of other options...

--data
declare @tblMyTable table (column1 int identity(1, 1), column2 varchar(20))
insert @tblMyTable
          select 'Apples'
union all select 'Oranges'
union all select 'Bananas'
union all select 'Corn'
union all select 'Green Beans'

--inputs
declare @inputVariable varchar(10)
set @inputVariable = 'Vegetable'

--calculation (one option)
SELECT column1
FROM @tblMyTable a inner join 
        (         select 'Fruit' as Type, 'Apples' as column2
        union all select 'Fruit', 'Oranges'
        union all select 'Fruit', 'Bananas'
        union all select 'Vegetable', 'Corn'
        union all select 'Vegetable', 'Green Beans'
        )  b
    on Type = @inputVariable and a.column2 = b.column2

--calculation (another option)
declare @t table (column2 varchar(20))
if @inputVariable = 'Fruit'
    insert @t select 'Apples' union all select 'Oranges' union all select 'Bananas'

if @inputVariable = 'Vegetable'
    insert @t select 'Corn' union all select 'Green Beans'

SELECT column1
FROM @tblMyTable
WHERE column2 IN (select column2 from @t)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

gqstud79
Starting Member

3 Posts

Posted - 2006-05-08 : 14:25:26

Thank you all for your help. I was able to solve the problem using the following syntax:

SELECT column1
FROM tblMyTable
WHERE (@inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources