[thelist] To Store or Not to Store - Building dynamic queries
Casey Crookston
caseyc at IntelliSoftmn.com
Mon Aug 14 14:54:15 CDT 2006
Ed (or anyone),
In the example you gave below, did you mean to say:
SELECT * FROM listings
WHERE (name LIKE @company_name OR name = '')
AND (address LIKE @address OR address = '')
In the OR, are we checking to see if the db vale is blank, or the
paramater value passed into the query is blank?
Thanks, Casey
-----Original Message-----
> my question is, how do I build a stored procedure that will allow
> for one or multiple WHERE statements.
One way I've used is to have a default value like a zero or an empty
string that gets passed when a criteria isn't specified.
SELECT * FROM listings
WHERE (name LIKE @company_name OR @company_name = '')
AND (address LIKE @address OR @address = '')
...
-----Original Message-----
I've got another "what's the best way" question.
For this phone book search engine site I'm working on, users will have
the option to search by one or multiple search criteria. Up to this
point, I've been putting all queries into a stored procedure. At this
point, I'm unsure how to proceed.
A query could look like:
SELECT * FROM listings WHERE name LIKE @company_name ORDER BY
company_name
Or, it could like:
SELECT * FROM listings WHERE name LIKE @company_name AND address LIKE
@address AND zip like @zip AND category LIKE @category ORDER BY
company_name
Or, it could be any number of combinations of these criteria. Building
the query is not a problem. A simple CASE SELECT statement will append
the needed WHERE statements. But, my question is, how do I build a
stored procedure that will allow for one or multiple WHERE statements.
Is it possible? Is it a good idea?
Thanks in advance for any tips!
Casey
More information about the thelist
mailing list