[thelist] To Store or Not to Store - Building dynamic queries
Casey Crookston
caseyc at IntelliSoftmn.com
Wed Aug 16 16:00:46 CDT 2006
Specific Example:
In the city of Elk River is a company called 'Abba Pregnancy Resource
Center' This query returns a correct result:
SELECT * FROM listings WHERE name LIKE 'Abba Pregnancy Resource Center'
AND (city IS NULL OR city LIKE 'Elk River')
But this query returns NO results:
SELECT * FROM listings WHERE name LIKE 'Abba Pregnancy Resource Center'
AND (city IS NULL OR city LIKE '')
If the user does not select a city, then the query needs to be:
SELECT * FROM listings WHERE name LIKE 'Abba Pregnancy Resource Center'
-----Original Message-----
>> WHERE @city IS NULL OR city LIKE @city <<
I'm not following the logic here. Let me reword this:
WHERE @passedValueCity IS NULL or dbColumnCity LIKE @passedValueCity
So my new query looks like this:
SELECT listing_id, RANK, name, address, city, zip, heading, phone
FROM listings a,
FREETEXTTABLE(listings, *, @Search)
WHERE [KEY] = a.listing_id AND (@passedValueCity IS NULL or dbColumnCity
LIKE @passedValueCity)
ORDER BY RANK DESC, name
I altered my query as such, and it returns no resluts either way - if
passedValue has a value of if it doesn't. (it also doesn't work if I
take off the () around the new AND clause.)
I hope I am making sense.
Thanks,
Casey
-----Original Message-----
> Now, I need to modify this to allow for the user having defined a city
> to search within. This would be the obvious addition
> (assuming @city is
> declared) :
>
> WHERE [KEY] = a.listing_id AND city LIKE @city
>
> But, this does not allow for the possibility the user might have NOT
> defined a city, in which case the "AND city LIKE @city"
> clause needs to
> not exist.
>
> So, I'm back to my original question: How do I dynamically
> include "AND
> city LIKE @city" in my stored proc?
WHERE @city IS NULL OR city LIKE @city
Jason
--
More information about the thelist
mailing list