>Two questions

>first,....suppose, in the original example, all the user entered was 'NY' into the state textbox, and leaves all the other ones blank.

>And let's even say that you're asking the user if they want a complete search, or 'begins with', or 'contains'....and that the user selects 'complete search'.

>Am I correct in saying that you'd construct the following query...

>"select firstName, lastname, Address, city, State, Zip from customers where state = 'NY'"

I depends... normally I'd construct a query using parameters (in VFP).

select firstName, lastname, Address, city, State, Zip from customers where state = ?m.State

This would cache the execution plan.
But it is not always possible to use parameters. For example if you want to parameterize a list:

select firstName, lastname, Address, city, State, Zip from customers where state IN(?m.Statelist)

The above won't work, so you'll have to embed it into the list. Another scenario where you cannot use parameters is when you want to create a persistent temp table on the SQL server. If you create a SQL statement with parameters it will use SP_ExecuteSQL() and any temp table will be released when the SP finishes.

And when you embed a 'parameter' into the SQL statement you'll have to watch out for pitfalls, like using single qoutes in the 'parameter'.


>Second, suppose the user says 'exact match....state....NY'....and then 'contains....address....BLVD'. Am I correct in saying that you'd construct the following query and execute it?

>"select firstName, lastname, Address, city, State, Zip from customers where state = 'NY' and address like '%BLVD%' "

Again, I'd likely use parameters in stead of hardcoding them, if it only were for ease of use avoiding SQL injection and errors with quotes in the embedded parameters values.

select  firstName, lastname, Address, city, State, Zip from customers where state = ?m.State and address like @m.Address