Re: Dynamic vs. Static SQL

[CRPence <CRPbottle@xxxxxxxxx>]

On 14 Jun 2013 05:34, Charles Wilt wrote:
> Never use dynamic unless you have too :)
>
> <<SNIP>>
>
> Off the top of my head, the only place you have to use a dynamic
> statement is when you need the table name to be variable. You also
> can't use a parameter here:
>
>   stmt = 'select * from ' + tableName + ' where myfld = 1';

  Even that does not meet the criteria for "have to use a dynamic 
statement".  Using an override with static SQL would be a better choice 
IMO.  Even an effectively dynamic ALIAS would suffice [albeit I always 
suggest against such /temporary/ use of an ALIAS, even if created in 
QTEMP] in place of an override.

  Before running the static SQL, assign the /variable/ table-reference; 
i.e. where the value of "MYTABLE" comes from whatever actual or 
effective variable:

   OVRDBF OVERRIDDEN TOFILE(*LIBL/MYTABLE) OVRSCOPE(as_required)

    *or*

   CREATE OR REPLACE ALIAS OVERRIDDEN ON MYTABLE /* naming=*SYS */

  Then the static SQL referencing the label OVERRIDDEN as the 
table-reference:

    exec SQL
        select * into :myDS from OVERRIDDEN where myfld = :myVar ;

  Ideally the program would get compiled with the override in effect or 
the ALIAS existing in the appropriate library [within the library list], 
to enable the Access Plan for the statement to be created then [if 
possible], as opposed to the creation being delayed until when the 
program is first run.