Example select in dynamic SQL

From SQL PL Guide for DB2

Example: Select statement in dynamic SQL

If you want to use a select or a values statement in dynamic SQL you can not use the EXECUTE IMMEDIATE statement. Instead of that you've to use the PREPARE, OPEN, FETCH and CLOSE statement.

In this example you see how to use it. The procedure just returning the number of rows in a table:

create procedure TABCOUNT (in tabschema varchar(20), in tabname varchar(100), out num_rows integer)
language sql
begin
  declare sqlstmt varchar(1000);
  declare mycur cursor for stmt;

  set sqlstmt = 'select count(*) from ' concat tabschema concat '.' concat tabname  ;

  prepare stmt from sqlstmt;
  open mycur;
  fetch mycur into num_rows;
  close mycur;
end!