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.
| Author |
Topic |
|
maya_zakry
Constraint Violating Yak Guru
379 Posts |
 Posted - 2007-02-09 : 03:12:31
|
| hello,from one of my solution, i havent had time untill now to ask detail what is this coalesce and nullif? Is nullif just like the isnull function? while coalesce is to replace null? can someone explain base on this eg??d.LocID >= coalesce(nullif(@LocFrom, ''), d.LocID) andd.LocID <= coalesce(nullif(@LocTo, ''), d.LocID) and~~~Focus on problem, not solution~~~ |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-09 : 03:14:36
|
From Books online: quote:
NULLIFReturns a null value if the two specified expressions are equivalent.
quote:
COALESCEReturns the first nonnull expression among its arguments.
So from your example:NULLIF(@LocFrom, '') - returns NULL if @LocFrom = ''Coalesce(NULLIF(@LocFrom, ''), d.LocID) - will return @LocFrom if it is not '', else it will return d.LocID in effect satisfying the WHERE clause always.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
maya_zakry
Constraint Violating Yak Guru
379 Posts |
Posted - 2007-02-09 : 03:26:13
|
| ooo thanks.. the nullif is clear, the coalesce is still quite blur... when do u use coalesce? so if @LocFrom='' and @LocTo='' then is actually d.LocID >= coalesce(null, d.LocID) andd.LocID <= coalesce(null, d.LocID) andthis where clause find non null of column d.locid?? all excluding null isit??? but how about this one?? COALESCE(UDF10, '%') LIKE '%'+@UDF10+'%'~~~Focus on problem, not solution~~~ |
|
|
|
maya_zakry
Constraint Violating Yak Guru
379 Posts |
Posted - 2007-02-09 : 03:34:00
|
| coalesce accept 2 args(1st,2nd) what is 1st type of data and 2nd type of data? how do we manipulate it... sorry if this quite silly Q :P~~~Focus on problem, not solution~~~ |
|
|
|
maya_zakry
Constraint Violating Yak Guru
379 Posts |
Posted - 2007-02-09 : 03:39:10
|
| what it return if both args is NULL? thanks~~~Focus on problem, not solution~~~ |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-09 : 03:42:25
|
run this and see the result. declare @arg1 int, @arg2 int, @arg3 intselect @arg1 = NULL, @arg2 = NULL, @arg3 = NULLselect arg1 = @arg1, arg2 = @arg2, arg3 = @arg3, [coalesce(@arg1, @arg2, @arg3)] = coalesce(@arg1, @arg2, @arg3)select @arg1 = NULL, @arg2 = 2, @arg3 = 3select arg1 = @arg1, arg2 = @arg2, arg3 = @arg3, [coalesce(@arg1, @arg2, @arg3)] = coalesce(@arg1, @arg2, @arg3)select @arg1 = 1, @arg2 = 2, @arg3 = 3select arg1 = @arg1, arg2 = @arg2, arg3 = @arg3, [coalesce(@arg1, @arg2, @arg3)] = coalesce(@arg1, @arg2, @arg3)
KH |
|
|
|
maya_zakry
Constraint Violating Yak Guru
379 Posts |
Posted - 2007-02-09 : 03:49:55
|
| wow khtan!!that really helps :D ... no more blurs~~~Focus on problem, not solution~~~ |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-09 : 03:52:30
|
just a note. If your front end application is able to pass in NULL instead of '' when the user did not select anything, wouldn't it make thing slightly easier ? Then you do not have to use the nullif() to check.
KH |
|
|
|
maya_zakry
Constraint Violating Yak Guru
379 Posts |
Posted - 2007-02-09 : 20:03:49
|
| yup.. havent think of that :P, but i already practic it in my dates param only coz it cannot compare date as varchar~~~Focus on problem, not solution~~~ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-10 : 08:40:12
|
COALESCE is Ansi standard.ISNULL is not.COALESCE can take multiple parameters (it returns the first one which is NOT null, it returns null if ALL the parameters are null)ISNULL can only take two parameters.For my this means that I always use COALESCE - plus "IsNull" as a function name suggests to me something that will test if an expression is NULL and return True or False.NullIf() is something else  as Khtan has explained.Kristen |
|
|
|
|
|
|
|
|
coalesce and nullif