db2 create procedure using razor -

i using razorsql tool work db2. try create procedure contains if table not exist statement.

the problem having if table doesn't exist procedure has execute create table statements. trying co create procedure returns error (syntax error), can not execute more create table statement.

example:

 create procedure klementest.create_table  () language sql modifies sql data --reads sql data --contains sql begin atomic  if (not exists(select 'a' syscat.tables tabschema = 'klementest' ,       tabname='bendeldoba'))  create table klementest.bendeldoba (     bdd_id_bdd           integer                not null,     bdd_naziv            varchar(128)           not null,     bdd_mesecev          integer                not null default 0,     bdd_prispevki        integer,     bdd_procent          numeric,     bdd_racuni           integer,     bdd_datvpisa         date                   not null,     bdd_vpisal_uporabnik integer                not null default 0  );  alter table klementest.bendeldoba add constraint p_key_1 primary key (bdd_id_bdd);  end if;  end 

alter table causing problems. if comment it works, trying co execute smth

  create procedure klementest.create_table      ()     language sql     modifies sql data     --reads sql data     --contains sql     begin atomic  if (not exists(select 'a' syscat.tables tabschema = 'klementest' , tabname='bendeldoba'))  crete view def_schema.view1 select * sometable;  crete view def_schema.view2 select * someothertable;  end if;  end 

it works

where "syntax error" problem first create procedure query??

thank you

in db2, sql stored procedures bound statically in database. means static sql statements (i.e. ones don't execute using prepare/execute or execute immediate) checked , compiled when create stored procedure.

therefore, error occurs because when db2 checks alter table statement validity, klementest.bendeldoba not yet exist.

the best way resolve make alter table statement dynamic statement:

declare vsql varchar(1024);  -- portion of procedure creates table...   set vsql = 'alter table ...'; execute immediate vsql;