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

Casey Crookston caseyc at IntelliSoftmn.com
Fri Aug 11 12:11:49 CDT 2006


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