[thelist] Dynamic vs. static SQL in large search screens

[John.Brooking at sappi.com]

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.