CODEWITHSUNDEEP
sqlstored-proceduresdb2callresultset
Thevagabond
Thevagabond

Reputation: 323

Execute SQL command from within a resultset in stored procedure in DB2

I have the following stored procedure:

CREATE PROCEDURE SQLTEST()
LANGUAGE SQL
BEGIN ATOMIC
DECLARE SQLCMD VARCHAR(1024);

FOR v AS cur1 CURSOR FOR SELECT ID, CMD from COMMANDTBL
DO 
  SET SQLCMD= v.CMD;
  "CALL" SQLCMD;
END FOR;
END?

The COMMANDTBL has the columns ID and CMD of which CMD has SQL Commands. CMD is 1024 VARCHAR. In this test they are 2 inserts for another table 

INSERT INTO TARGETTBL(TARGET, TARINT) VALUES ('TEST', 100);
INSERT INTO TARGETTBL(TARGET, TARINT) VALUES ('TEST2', 200);

for example.

My problem is the "CALL" part. I have DB2 v9.7 but it would be great if there was a solution running on lower versions as well. I have not found any pointers on how to run this and a simple EXEC or EXECUTE does not work.

Thank you for your help.

TheVagabond

Upvotes: 0

Views: 295

Answers (1)

data_henrik
data_henrik

Reputation: 17156

You need to prepare and execute the statements because they are deemed "dynamic SQL".

PREPARE myStmt FROM SQLCMD; EXECUTE myStmt;

Upvotes: 1

Related Questions