Hi Evan,
I used the approach of dynamic SelectCMD for Search form where we don't know exactly what the criteria may be. For this scenario and the one you're now describing I agree, using dynamic SelectCMD sounds like a good idea. It would be hard to handle it through parameters.
We have the same problem now in ASP.NET with the complex search criteria. Our current way now is to build Select command using string concatenation. I don't like this approach but could not come up with a better one using stored procedure.
>Naomi,
>
>Because you may want to query using more parameters than just the Customer ID.
>
>For instance, you might have an option on a particular form to show "past" addresses, or addresses that are only good for a certain period of time (ex. - many elderly snowbirds have two addresses, one for summer and one for winter) -- so you'd add a check for an "active" boolean or possibly a range of effective dates. You might want to filter the addresses for a single ZIP code. You might want to show related family member's addresses -- so you might want to send a list of CustomerID values -- or use LIKE instead of = to bring back surrounding addresses (like for a mailing list to neighbors).
>
>I can think of **LOTS** of different permutations of this...and you can handle them all with a single CA object. Using your approach, you'd now need *several* additional CA's, all indexed in different ways, to handle all the particular options. Managing those additional objects can get cumbersome if you're dealing with lots of child tables, as Matt stated.
>
>Also, with my suggestion, you could (if desired) store all the necessary SelectCmd possibilities in a SQL table of their own and choose the one you wanted as needed or required by using a "keyword" -- thereby, your entire data querying mechanism is itself "data driven".
>
>What I showed Matt was just one approach. Personally, I don't use DE's, so my personal approach is a bit different -- but Matt uses them, so I was tailoring my answer to his methodology.
>
>Evan
>
If it's not broken, fix it until it is.
My Blog