case statement run if NULL or 0

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 statement run if NULL or 0

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2004-12-08 : 11:20:07

Hi everyone,

I have a CASE statement that needs to be ran when a value in my column is equal to NULL or 0. How can I get this to work?

I've tried using the IN, and BETWEEN operators...


CASE WHEN ColumnName IN (Null, 0) THEN

CASE WHEN ColumnName BETWEEN Null and 0 THEN

Is what I am trying to do possible, or should I just make two CASE statements. One for the NULL and one for the 0 value?

Thanks everyone!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 11:23:51

CASE WHEN ColumnName is null or ColumnName = 0 THEN

Go with the flow & have fun! Else fight the flow

hueby
Posting Yak Master

127 Posts

Posted - 2004-12-08 : 11:25:50

Ahh thank you, I was trying CASE WHEN ColumnNAME is NULL or 0 THEN ... I see now! Thank you!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-12-08 : 11:29:45

quote:
Originally posted by hueby

Ahh thank you, I was trying CASE WHEN ColumnNAME is NULL or 0 THEN ... I see now! Thank you!

or even

CASE WHEN isnull(ColumnNAME, 0) = 0 THEN

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 11:32:24

i wonder what's faster....

there i go again with my speed obssesion...

Go with the flow & have fun! Else fight the flow

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-12-08 : 11:37:35

quote:
Originally posted by spirit1

i wonder what's faster....

there i go again with my speed obssesion...

Go with the flow & have fun! Else fight the flow

HA HA

they're exactly the same but one is shorter that's all

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 11:41:10

really?? i thought that functions would be slower than a "condition1 OR condition2"....

Go with the flow & have fun! Else fight the flow

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-12-08 : 11:42:44

quote:
Originally posted by spirit1

really?? i thought that functions would be slower than a "condition1 OR condition2"....

Go with the flow & have fun! Else fight the flow

Well,


select case when isnull(0, 0) = 0 then 'RIGHT' else 'WRONG!' end
select case when 0 is null or 0=0 then 'RIGHT' else 'WRONG!' end

are exactly the same according to the execution plan. I don't know...

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 11:50:41

interestingly enough both give same execution plan but
elapsed time of set statistics time gives results in favor of "OR"


use northwind
select * from orders
where isnull(shipregion, '') = ''
order by shipregion

select * from orders
where shipregion = '' or shipregion is null
order by shipregion

Go with the flow & have fun! Else fight the flow

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-12-08 : 11:53:49

quote:
Originally posted by spirit1

interestingly enough both give same execution plan but
elapsed time of set statistics time gives results in favor of "OR"

Fair enough...

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 11:56:49

but it's still intriguing, no?? how can 2 same exec plans be different in speed??

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources