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 |
|
arusu
Yak Posting Veteran
60 Posts |
 Posted - 2010-08-03 : 10:51:56
|
| Hi,i have a query that i need.lets say you have a table with a person_id, person_name and citythe query needs to allow you to get the person_id based on the name and city.so select person_id where name = ? and city = ?The problem that i have is that i need something to handle this even if an city was not supplied.... so if the parameter passed for city is null then you would still run the query and get all of the person_id's just based on the name. Basically city is an optional parameter if something valid is passed |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-03 : 10:56:50
|
| Whats wrong with all suggestions provided in this threadhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148072 |
 |
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2010-08-03 : 11:22:28
|
| The problem is that it does not return any rows. None of those suggestions work |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-03 : 11:38:11
|
| You need to tell us which suggestion you tried...and the actual query you used. |
|
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2010-08-03 : 11:50:13
|
| ok well i tried your suggestion and and it does not work, also no offense but i think it's a hack solution even if it did work.I tried with coalesce and it does not work either |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-03 : 11:57:23
|
| Now...you need to provide more information.Show us the entire query you tried. How are you executing it. Is this a stored procedure? If it is, what will be the value for the variable, if no data is supplied. Will it be NULL?If it were NULL, then the queries provided should work. |
|
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2010-08-03 : 11:59:15
|
| got it with coalesceand COALESCE(city, 'null') = COALESCE(@city, city, 'null')thanks for your help anyways |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-03 : 12:34:20
|
quote:
Originally posted by arusu
got it with coalesceand COALESCE(city, 'null') = COALESCE(@city, city, 'null')thanks for your help anyways
COALESCE is probably the worst solution to this problem. FYI. |
|
|
|
|
|
|
|
|
if not null