August 6, 2012 at 7:56 am
What's the best way to add columns which have null values in TSQL? Is there a more efficient way than using a CASE statement?
August 6, 2012 at 8:00 am
Can you clarify the question? Are you talking about adding a column to a table or to a result set?
Jared
CE - Microsoft
August 6, 2012 at 8:03 am
I think the OP means this:
SELECT CASE WHEN Col1 IS NULL THEN 0 ELSE Col1 END + CASE WHEN Col2 IS NULL THEN 0 ELSE Col2 END ?
SELECT ISNULL(Col1, 0) + ISNULL(Col2, 0) or COALESCE(Col1, 0) + COALESCE(Col2, 0) both work, couldn't comment on their relative speeds though.
August 6, 2012 at 8:07 am
Gazareth (8/6/2012)
I think the OP means this:SELECT CASE WHEN Col1 IS NULL THEN 0 ELSE Col1 END + CASE WHEN Col2 IS NULL THEN 0 ELSE Col2 END ?
SELECT ISNULL(Col1, 0) + ISNULL(Col2, 0) or COALESCE(Col1, 0) + COALESCE(Col2, 0) both work, couldn't comment on their relative speeds though.
There was a discussion on this awhile back: http://www.sqlservercentral.com/Forums/Topic832742-392-1.aspx
Jared
CE - Microsoft
August 6, 2012 at 8:27 am
isnull did the trick, thanks
August 6, 2012 at 11:24 am
Take a look at Coalesce while you're getting into this. It has advantages over IsNull, in that it can have more than 2 arguments. Plus, it is part of the ANSI/ISO SQL standard, and IsNull isn't, which is a minor advantage, but is one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply