Tablename as variable in DB2 for loop procedure
I would like to convert the following script from a static sql statement for loop FOR
in DB2 to dynamic. concretely taking the table name from the procedure parameters and using it in the SQL statement in a loop FOR DO
.
The starting point is the script:
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN ATOMIC
DECLARE fullname CHAR(40);
FOR v AS cur1 CURSOR FOR
SELECT firstnme, midinit, lastname FROM employee
DO
SET fullname = v.lastname || ',' || v.firstnme
||' ' || v.midinit;
INSERT INTO tnames VALUES (fullname);
END FOR;
END
I would like to do something like this:
CREATE PROCEDURE P(IN TABLENAME VARCHAR(128) DEFAULT 'TABLE1')
LANGUAGE SQL
BEGIN ATOMIC
DECLARE fullname CHAR(40);
FOR v AS cur1 CURSOR FOR
SELECT firstnme, midinit, lastname FROM TABLENAME
DO
SET fullname = v.lastname || ',' || v.firstnme
||' ' || v.midinit;
INSERT INTO tnames VALUES (fullname);
END FOR;
END
Is there a way to do this? I've tried STATEMENT
but I can't seem to get it to work with FOR LOOP
.
source to share
I managed to get it to work without FOR LOOP
using this method:
CREATE OR REPLACE PROCEDURE P(IN TABLENAME VARCHAR(100) DEFAULT 'TABLE1')
LANGUAGE SQL
BEGIN
DECLARE fname VARCHAR(100);
DECLARE mdinit VARCHAR(100);
DECLARE lname VARCHAR(100);
DECLARE dumy VARCHAR(100) DEFAULT 'POP';
DECLARE stmt STATEMENT;
DECLARE cur1 CURSOR FOR stmt;
SET text = 'SELECT firstnme, midinit, lastname FROM ' || TABLENAME;
PREPARE stmt FROM text;
OPEN cur1;
fetch_loop:
LOOP FETCH cur1 INTO fname,mdinit,lname;
EXECUTE IMMEDIATE 'update '|| TABLENAME || ' set midinit=' || dumy || ' where firstname = ' || fname;
END LOOP fetch_loop;
CLOSE cur1;
END
But the solution for FOR LOOP
source to share