SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 check for nulls in case statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vmurali
Yak Posting Veteran

India
88 Posts

Posted - 07/13/2007 :  06:02:09  Show Profile  Send vmurali a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 07/13/2007 :  06:34:55  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

India
22765 Posts

Posted - 07/13/2007 :  08:30:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 07/13/2007 :  08:33:28  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

India
22765 Posts

Posted - 07/13/2007 :  08:40:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Slovenia
11751 Posts

Posted - 07/13/2007 :  09:04:01  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 07/13/2007 :  09:04:18  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Slovenia
11751 Posts

Posted - 07/13/2007 :  09:25:28  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

India
22765 Posts

Posted - 07/13/2007 :  23:48:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 07/14/2007 :  14:17:41  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

India
22765 Posts

Posted - 07/16/2007 :  02:26:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 Posts

Posted - 08/04/2007 :  12:46:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000