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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-08-08 : 16:22:53
|
| I give users catch-all ASP search forms running against SQL Server 7.0 that allow them to search various tables based on whichever combination of fields they choose to fill in on the forms. Note: users can leave as many fields blank as they like. I then send the parameterized query to the SQL Server (or run an SPROC on the server side) See rough example that follows. Don't have the code in front of me so syntax is not right but you get the gist... SELECT * FROM TABLE WHERE firstname=textboxvalue1 AND lastname=textboxvalue2 etc...I have found that SQL Server will ignore records in the base tables that have one or more NULL entries in various fields, even if these records meet the user's search criteria.Anyone know how to make sure records that meet the search criteria are returned, even those records with NULL entries in various fields?thx!Edited by - steelkilt on 08/08/2002 16:23:54Edited by - steelkilt on 08/08/2002 16:24:56 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-08 : 19:56:08
|
quote:
Note: users can leave as many fields blank as they like.
When a user leaves a field blank, what do you pass to the database? If an empty string, that's likely the cause of the problem. A NULL does not equal "". You will need to explicitly check using IS NULL if that is in fact the case.Jonathan Boott, MCDBA{0} |
 |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-08-09 : 09:11:33
|
| Thanks. I'm red-faced that I missed this one! How does this look... SELECT * FROM TABLE WHERE firstname=textboxvalue1 OR firstname IS NULL AND lastname=textboxvalue2 OR lastname IS NULL etc... thx |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-09 : 09:16:23
|
I think you are going to have an order of operations problem like that. Use this insteadSELECT * FROM TABLE WHERE firstname=coalesce(nullif(textboxvalue1,''),firstname) and lastname=coalesce(nullif(textboxvalue2,''),lastname) and etc....EDIT: Not to mention your friends will think you are cooler cause you used the COALESCE fruction!!  EDIT (again): Your friend will not think you are cool if you don't read the question correctly...  Jay White{0}Edited by - Page47 on 08/09/2002 09:17:28Edited by - Page47 on 08/09/2002 09:20:08 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-09 : 09:17:29
|
| The problem is that if the user DOES pass something to find for that column, it will return Nulls anyway. Plus you have to put parentheses around OR'd clauses when also using AND. Try this:SELECT * FROM TABLE WHERE (firstname=textboxvalue1 OR NullIf(firstname,'') IS NULL)AND (lastname=textboxvalue2 OR NullIf(lastname,'') IS NULL)AND ... |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-08-09 : 09:29:38
|
| Hey, thanks for all the input. Beautiful SQL statements! |
|
|
|
|
|
|
|
|
ASP script ignores records with NULLs??