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 |
|
slacker
Posting Yak Master
115 Posts |
 Posted - 2003-05-21 : 20:37:51
|
| I just used the tutorial for dynamic where clause here..http://www.sqlteam.com/item.asp?ItemID=2077Imediately i thought to myself.. what if the column in question is null. It wont return then. I tested it using code below to verify it. Then i thought.. well ill just shut off ansi_null. Still doesnt work.. I found that I had to use the actual word null in order for it to ever work. for instance.. city=city if they are both null wont return anything. Even with ansi_nulls off. Anybody have a suggestion? Really fields like this shouldnt contain null. But the table is poorly designed. I will probably redesign it but im still curious. Is there a reason that i have to specify NULL in order for it to work instead of using a variable that contains null or a function that returns null...-- /// Some code to demonstrate what i mean.--create tablecreate table testcoalesce( city varchar(100), state varchar(100), zip varchar(100))--insert some dummy datainsert into testcoalesce (city,state,zip) values(null,'test1','test1')insert into testcoalesce (city,state,zip) values(null,'test2','test2')insert into testcoalesce (city,state,zip) values(null,'test3','test3')insert into testcoalesce (city,state,zip) values('test4','test4','test4')-- turn off ansi nulsset ansi_nulls offdeclare @city varchar(100)set @city = null--test dynamic where clause with coalesce and null fieldsselect * from testcoalesce where city = coalesce(city,@city)-- only row four with non-null city returns. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-05-21 : 21:11:43
|
| Tryselect * from testcoalesce where (@city IS NULL OR city =@city) It's a somewhat better syntax IMHO.Sam |
 |
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-05-21 : 21:33:02
|
| Thanks alot i agree it is better syntax. Why i didnt think of it is a mystery :). Maybe i rely too much on cut and pasting articles :). |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-05-21 : 22:10:01
|
Don't blame yourself  this topic shows up now and then on this forum. Many strongly suggest the use of COALESE while others use IsNull , still others recommend the syntax above.Maybe tomorrow morning we'll get some of the opinion of others on the subject.Me? I use what seems best for the situation...Sam- 6,217 more posts to become a MVP like Rob. |
|
|
|
|
|
|
|
|
coalesce and nulls.