[thelist] To Store or Not to Store - Building dynamic queries

Casey Crookston caseyc at IntelliSoftmn.com
Wed Aug 16 15:44:42 CDT 2006


>> 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
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt ! 





More information about the thelist mailing list