Hello, evolters, Here is a general design question I've been wrestling with recently, and I wondered what some of you here might have to say about it. We know that in database-driven web applications, it is considered best practice to separate the presentation and database layers, so here at my company we generally put all the SQL inside stored procedures. Furthermore, inside the stored procedures, there is the concept of static vs. dynamic SQL. Static means the statement stays the same between executions (except parameter values) and so can be compiled ahead of time. Dynamic means the statement is not parsed until run-time, just prior to execution. Since dynamic is more expensive, we try to use static whenever possible. Here's the dilemma. It seems to be common for search screens (especially) to have so many options that it is very difficult, if not impossible, to define static SQL to execute it. One screen we've done has about a dozen fields, any combination of which may be used to filter and sort. To define a series of static SQL statements to cover all the possible combinations of fields used would be a nightmare, both to initially code and to maintain. So instead, we put the SQL statement together dynamically according to what fields are being used. My questions are: 1. Is using a dynamic SQL statement in this scenario a standard thing to do, or is there a better way? 2. If so, where is the best place to put the logic to build the SQL statement? Pure presentation/database separation would dictate passing all the potential parameter values to the stored procedure and letting it build the statement, but that could mean passing in a large (dozen or more) number of parameters, most of which would likely be NULL. Or, the presentation layer could build the SQL statement and just pass it to the database to execute, but this would violate the separation principle and possibly making more rewrite work if the presentation platform changes in the future. Any compromises I could think of would be just as much work on the presentation end without the benefit. I'd be interested in hearing your thoughts on this issue. Of course the exact decisions will depend on the context, but can any general guidelines be formulated? - John Web Developer, Sappi Fine Paper 600 Southborough Drive South Portland, ME, 04106 USA Email: john.brooking at sappi.com Voice: 207-854-7058 FAX: 207-854-7141 -- This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.