Dear Support!

I used FB formerly and in this RDBMS the procedures are static.

This meaning that on creation the RDBMS is eval the fields, SQL-s, and compile the stored procedure.
If some fields modified, I need to recreate the stp, and dynamic SQL/fields/tables are not supported.

But in EDB I can create magic STPs:

alter PROCEDURE "test" (
 IN "tnev" VARCHAR(100) COLLATE ANSI,
 IN "oldkod" INTEGER,
 IN "fnev" VARCHAR(100),
 in "newkod" integer)
BEGIN
  declare tmptablanev varchar(100);
  declare sql varchar(500);
  declare rc integer;
  DECLARE TempCursor CURSOR FOR tstmt;
  set tmptablanev = 'tmp_copyr_' || cast(oldkod as varchar(12));
  /* Same table exists or not? */
  /*PREPARE tstmt FROM 'SELECT Name FROM information.TemporaryTables where lower(name)= lower(?)'; */
  PREPARE tstmt FROM 'SELECT Name FROM information.Tables where lower(name)= lower(?)';
  OPEN TempCursor USING tmptablanev;
  set rc = ROWCOUNT(TempCursor);
  CLOSE TempCursor;
  IF (rc > 0) THEN
     /* Yes, drop it */
     set sql = 'drop table ' || tmptablanev;
     execute immediate sql;
  END IF;
  /* Copy table */
  set sql = 'create ' || /*temporary*/ ' table ' || tmptablanev;
  set sql = sql || ' as ';
  set sql = sql || ' SELECT * from ' || tnev;
  set sql = sql || ' where ' || fnev || ' = ' || cast(oldkod as varchar(12));
  set sql = sql || ' with data';
  execute immediate sql;
  /* Renew the IDs */
  set sql = 'update ' || tmptablanev;
  set sql = sql || ' set ' || fnev || ' = ' + cast(newkod as varchar(12));
  execute immediate sql;
  /* Recopy to the original table */
  set sql = 'insert into ' || tnev;
  set sql = sql || ' select * from ' || tmptablanev;
  execute immediate sql;
  /* Drop it */
  set sql = 'drop table ' || tmptablanev;
  execute immediate sql;
END

This can copy the data of a table ot itself - with new ids (for child copy).

I want to say that I hope you will not change this feature in the future...

Or are you want to change this?

So we can build our programs to this dynamic SQL or not?

Thanks:
  dd

<< I want to say that I hope you will not change this feature in the
future... >>

No, absolutely not.  The SQL/PSM was designed this way to make it very easy
to mix DML and DDL together, and will never change.

<< So we can build our programs to this dynamic SQL or not? >>

Absolutely.

--
Tim Young
Elevate Software
www.elevatesoft.com