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 2005 Forums
 Transact-SQL (2005)
 check for nulls in case statement

Author  Topic 

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-07-13 : 06:02:09
Hi,
Actually in my project I need to insert record into a table. I ned to check for condition if null replace the value with 'Unknown'
I have written as

case custtype when null then 'Unknown' when 1 then 'AAA' else 'BB'
end

but its not working how do we check for nulls

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-13 : 06:34:55
use ISNULL() or Coalesce() function:

ISNULL(custtype, 'Unknown')




Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-13 : 08:30:02
Always use CASE WHEN expression instead of CASE expression WHEN

case when custtype is null then 'Unknown' when custtype = 1 then 'AAA' else 'BB'
end


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-13 : 08:33:28
quote:
Originally posted by madhivanan

Always use CASE WHEN expression instead of CASE expression WHEN



what's the reasoning behind this? please educate me.


elsasoft.org
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-13 : 08:40:51
quote:
Originally posted by jezemine

quote:
Originally posted by madhivanan

Always use CASE WHEN expression instead of CASE expression WHEN



what's the reasoning behind this? please educate me.


elsasoft.org


Why not?
Refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=86290

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-13 : 09:04:01
i agree with our prematurer on this one.
i have to say that using case when is more readable. at least for me.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-13 : 09:04:18
it just seems like a blanket statement to me. it's like saying, "never use the switch/case statement in C++, only use if/else if".

sometimes one or the other makes sense.

I guess you are saying one is more error-prone than the other?


elsasoft.org
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-13 : 09:25:28
acctully case @var when ... did behave strangly on occasion
but that was my error and i rewrote it using case when @var ....
that's why i don't like to use the first one anymore because i like the other version better.
simple as that.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-13 : 23:48:03
quote:
Originally posted by jezemine

it just seems like a blanket statement to me. it's like saying, "never use the switch/case statement in C++, only use if/else if".

sometimes one or the other makes sense.

I guess you are saying one is more error-prone than the other?


elsasoft.org


Did I suggest to use IF..ELSE.. instead of CASE WHEN...?

Self-explained example
1

Select
case when value>=1000 then 'something1'
when value>=2000 then 'something2'
.
.
end

2

Select
case value when>=1000 then 'something1'
when>=2000 then 'something2'
.
.
end


Will 2 work correctly?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-14 : 14:17:41
no it won't, because you aren't using "case" right in that "case".

I wasn't looking for education on the syntax - I know the syntax. I guess you mean not to use it simply because the other form can be used in all cases, whereas the case expression when syntax can only be used when testing for equality of a single expression among different values, so it's more restricted. In that sense, it's like switch/case in C++ (hence my comment above).


elsasoft.org
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-16 : 02:26:27
quote:
Originally posted by jezemine

no it won't, because you aren't using "case" right in that "case".

I wasn't looking for education on the syntax - I know the syntax. I guess you mean not to use it simply because the other form can be used in all cases, whereas the case expression when syntax can only be used when testing for equality of a single expression among different values, so it's more restricted. In that sense, it's like switch/case in C++ (hence my comment above).


elsasoft.org


Exactly
The newbies who start with Simple CASE function: as explained in BOL, try to similate the same for non-equality as well. Thats why I suggest CASE WHEN for checking for everything


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

saqi
Starting Member

1 Post

Posted - 2007-08-04 : 12:46:39
set ansi_nulls off --on
dbcc useroptions

declare @num char(10)
declare @value varchar(10)
select @num
set @value = case @num when null then 'null' else 'not null' end
select @value

The above code will just work fine. The code wont work when you set the ansi_nulls setting to ON. And thats wht the reason your case statement is failing. FYI, SQL 2K has this setting set to ON.

saq
Go to Top of Page
   

- Advertisement -