CASE inside IF ELSE 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

SQL Server Development (2000)

CASE inside IF ELSE statement

Author

Topic

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-06 : 23:03:41

I am trying to write a CASE statement inside IF ELSE statement but there is always error

declare @ecity varchar(20)
set @ecity = 'Aichi'
IF 2>1
CASE @ecity
WHEN 'Aichi' THEN PRINT 'OK'
END

what is wrong?

**Jonathan**

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 23:13:52

You can't use CASE like that. It can only use in a SELECT statement.


declare @ecity varchar(20)
set @ecity = 'Aichi'
IF 2>1
if @ecity=  'Aichi' PRINT 'OK'

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-06 : 23:30:23

So is this right?

declare @t table (ItemNo int, PartNo int, price int)

insert @t
select 1, 10, 100 union all
select 1, 20, 100 union all
select 1, 30, NULL union all
select 2, 10, 150 union all
select 2, 20, 100 union all
select 2, 30, 100

declare @ecity varchar(20)
set @ecity = 'Aichi'

select ItemNO,
(
IF 2>1
CASE @ecity
WHEN 'Aichi' THEN PRINT 'OK'
WHEN 'Tokyo' THEN PRINT 'SO SO'
WHEN 'Kyodo' THEN PRINT 'MAYBE'
END) as status
from @t

**Jonathan**

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 23:32:07

no. you can't use IF in a select statement. Try again

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-07 : 00:38:57

Thanks

then can I just use 2 CASE statement?

declare @t table (ItemNo int, PartNo int, price int)

insert @t
select 1, 10, 100 union all
select 1, 20, 100 union all
select 1, 30, NULL union all
select 2, 10, 150 union all
select 2, 20, 100 union all
select 2, 30, 100

declare @ecity varchar(20)
set @ecity = 'Aichi'

select ItemNO,
(
CASE
WHEN (2>1)
ELSE
CASE @ecity
WHEN 'Aichi' THEN PRINT 'OK'
ELSE PRINT 'BAD'
END
END) as status
from @t

**Jonathan**

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-07 : 00:40:34

please ignore the last post, this is the more correct one

declare @t table (ItemNo int, PartNo int, price int)

insert @t
select 1, 10, 100 union all
select 1, 20, 100 union all
select 1, 30, NULL union all
select 2, 10, 150 union all
select 2, 20, 100 union all
select 2, 30, 100

declare @ecity varchar(20)
set @ecity = 'Aichi'

select ItemNO,
(
CASE
WHEN (2>1) THEN 'OK'
ELSE
CASE @ecity
WHEN 'Aichi' THEN PRINT 'OK'
ELSE PRINT 'BAD'
END
END) as status
from @t

**Jonathan**

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 02:10:05

[code]
declare @t table (ItemNo int, PartNo int, price int)

insert @t
select 1, 10, 100 union all
select 1, 20, 100 union all
select 1, 30, NULL union all
select 2, 10, 150 union all
select 2, 20, 100 union all
select 2, 30, 100

declare @ecity varchar(20)
set @ecity = 'Aichi'

select ItemNO,
(
CASE
WHEN (2>1) THEN 'OK'
ELSE
CASE @ecity
WHEN 'Aichi'
THEN ~~PRINT~~ 'OK'
ELSE ~~PRINT~~ 'BAD'
END
END
) as status
from @t
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources