Re: Dynamic vs. Static SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Never use dynamic unless you have too :)

Dynamic is slower and opens the doors to SQL injection.

If you must use dynamic, make sure to use parameterized statements
stmt = 'select * from mytable where myfld = ?';

as opposed to
stmt = 'select * from mytable where myfld = ' + myVar;

Of course the above example doesn't need to be dynamic
/exec SQL
  select * into :myDS from mytable where myfld = :myVar;


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';


HTH,
Charles





On Fri, Jun 14, 2013 at 8:19 AM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:

> Anyone have a good explanation or link regarding when dynamic or static
> should be or must be used? I always make stuff work, but I don't know if I
> fully understand the distinction. Friday fodder. :)
>
> Thanks!
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.