What is the Difference of Static SQL and Dynamic SQL

penchalaiaht · New User Joined: 22 Mar 2006 Posts: 2 Location: Bangalore

Hi

could you give me the brief explanation for Static SQL and Dynamic SQL

William Thompson · Posted: Sun Jan 21, 2007 3:55 am

penchalaiaht,

FWIW (For What It's Worth):
Static has already been evaluated by the SQL optimizer and (hopefully) optimized for the best access to the data.
Dynamic has not (yet) been optimized and, with a throw of the dice, will get be optimized well (real time cpu goes here).
Static is, for instance, a select with all the conditions already decided, the actual values of the selection need to be decided but the structure of the select is already "poured in concrete", so to speak.
Dynamic is pretty much "freeflow", The actual conditions for the select have been determined at execution time.
Static can be "honed" to the most efficient select while, expending cpu real time, dynamic may or may not pick the best access path.

Is that enough?

Bill

DavidatK · Posted: Mon Jan 22, 2007 5:42 am

Penchalaiaht,

Static SQL is coded in your program and analyzed or prepared at compile time. You cannot change the SQL.

Dynamic SQL is constructed at execution time in working storage as a character field. (This would be done, perhaps, when a column exists in multiple tables or the column name you want to select is unknown until execution time) then prepared and executed. This method is not as efficient as static SQLs and may result in less than the best access to the data.

Please also take a look the links in this previous post Previous Post