Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Format 5 Dynamic SQL

How can I write a select statement where the table name is determined dynamically at run time?

There are many ways to create and execute SQL statements dynamically in PowerBuilder including the infamous "Formats 1, 2, 3 and 4 Dynamic SQL." SQL Anywhere 5 offers yet another way which we can call Format 5: The new Execute Immediate statement.

No, it's not the same as PowerBuilder's Execute Immediate, it's different and more powerful in several ways. First of all it is a SQL statement rather than a Powerscript command and so you can place it inside stored procedures and functions. There are also fewer restrictions on what kind of statements can be executed dynamically.

Here is a SQL Anywhere 5 stored function that returns xxx_name for a given value of xxx_code where the table name "xxx" is passed as a parameter:

   drop function f_name\

   create function f_name
      ( as_table_name char ( 100 ),
        as_code char ( 2 ) )
   returns char ( 40 )

   begin

      declare ls_select char ( 1000 );
      declare ls_name   char ( 40 );

      set ls_select = 'select '
         || as_table_name
         || '_name into ls_name from '
         || as_table_name
         || ' where '
         || as_table_name
         || '_code = '''
         || as_code
         || '''';

      execute immediate ls_select;

      return ( ls_name );

   end\

Here's an example of a DataWindow select statement in PowerBuilder 5's new programmer-friendly "Syntax Mode" display:

And just to prove it works, here's the DataWindow in preview mode: