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

[Casey Crookston]

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