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 |
|
t1g312
Posting Yak Master
148 Posts |
 Posted - 2005-01-03 : 08:35:32
|
| Hi all,I'm trying to implement a search feature using stored procedures. Basically the search looks something like this:select field1, field2... from TableName where field1 = isnull(@field1,field1)...It works fine as long as field1 is not null. If it is null, the row isn't returned. What do I do?Thanks in advance.Adi-------------------------/me sux @sql server |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-03 : 08:50:40
|
maybe?... where field1 = @field1 or @field1 is nullGo with the flow & have fun! Else fight the flow  |
 |
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-01-03 : 23:41:17
|
| The problem is that when the value is null, SQL Server expects IS NULL and = value otherwise and I can't predict the value of the field.Adi-------------------------/me sux @sql server |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-04 : 00:02:22
|
| Do you mean you want the test to be ignored if @field1 is null? then replace where field1 = isnull(@field1,field1)withwhere (field1 = @field1 or @field1 is null)if @field1 is null then the expression will always be true and so not affect the filter.if you want it to just return those rows where field1 is null thenwhere (field1 = @field1 or (@field1 is null and field1 is null))==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-01-04 : 00:28:35
|
| Thanks nr! where (field1 = @field1 or @field1 is null) was exactly what I was looking for.Adi-------------------------/me sux @sql server |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-04 : 00:56:27
|
| well spirit1 said it first but...==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-01-04 : 02:16:31
|
| Sorry, I meant (field1 = @field1 or (@field1 is null and field1 is null))I think I've been working too hard :SAdi-------------------------/me sux @sql server |
|
|
|
|
|
|
Null Values