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

jason.handby jason.handby at corestar.co.uk
Wed Aug 16 16:16:04 CDT 2006


Hi Casey,


If the user has not selected a city, will the passed in city parameter
(in @passedValueCity) be NULL or will it be an empty string ('')?

If it will be NULL, then you should use

  @passedValueCity IS NULL OR city LIKE @passedValueCity

If it will be '' then you should use

  @passedValueCity = '' OR city LIKE @passedValueCity


The logic is that, if the passed-in variable is NULL or '', the left
hand side of the OR will evaluate to true for all rows and so it doesn't
matter what the right hand side is. All rows will evaluate this
expression to be true.



The specific example you give is wrong...

Assume that your passed-in variable is NULL if no city is selected.

The query would then be

 SELECT * FROM listings WHERE name LIKE 'Abba Pregnancy Resource Center'
 AND (@passedValueCity IS NULL OR city LIKE @passedValueCity)


If your value of @passedValueCity is 'Elk River' then this will expand
out to

 SELECT * FROM listings WHERE name LIKE 'Abba Pregnancy Resource Center'
 AND ('Elk River' IS NULL OR city LIKE 'Elk River')

(notice how that's different from the first query in your example?)


The second query will expand out to

 SELECT * FROM listings WHERE name LIKE 'Abba Pregnancy Resource Center'
 AND (NULL IS NULL OR city LIKE NULL)

And of course (NULL IS NULL) will evaluate to true, so it doesn't matter
what the value in the city column is.



I promise you this will work!





Jason



More information about the thelist mailing list