CODEWITHSUNDEEP
sqlstored-proceduresparameterscursordb2
Thevagabond
Thevagabond

Reputation: 323

Use in parameter as table name in stored procedure inner sql definition

Since a few changes were made during the answers here is the working procedure:

CREATE OR REPLACE PROCEDURE SQLTEST(in intab VARCHAR(50))
LANGUAGE SQL
BEGIN ATOMIC
DECLARE SQLCMD VARCHAR(1024);
DECLARE tabname VARCHAR(50);
SET tabname=intab;

FOR v AS cur1 CURSOR FOR SELECT ID, CMD from COMMANDTBL
DO 
  SET SQLCMD = REPLACE(v.CMD, 'TABREPL', tabname); 

PREPARE myStmt FROM SQLCMD; 
EXECUTE myStmt;
END FOR;
END@

As shown I want to call the procedure with an in parameter that is to be the table name in the inner sql definition.

Calling it is: 

CALL SQLTEST('targettbl')@

Thank you.

TheVagabond

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Does doing the logic in a single expression work?

SET SQLCMD = REPLACE(v.CMD, 'TABREPL', tabname);

Upvotes: 1

Related Questions