I have a table (say Table_1 with one column) in which a macro will insert the DML statements (insert statements). I need to write a SP to query this table (just select from table_1) and the result of that needs to be executed so that it will do the DML operation...I have tried the below proc, but as i have never written SPs so far, it looks like I cant get it to work...any help is appreciated!
replace PROCEDURE new_proc_test (OUT test_stmt varchar(4000))
DECLARE SqlStr VARCHAR(4000);
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'sel * from table_1;';
PREPARE S1 FROM SqlStr;
FETCH C1 INTO test_stmt; ----this is where i need to execute the step instead of simply fetching it (call the dbc.execsql or execute immediate which doesnt work
Solved! Go to Solution.
If test_stmt contains the name of a macro, then I think you have to add yet another cursor in you declarations like
DECLARE M1 CURSOR FOR Macro1;
then after the Fetch do something like
set :test_stmt = 'Exec ' || :test_stmt;
PREPARE Macro1 from :test_stmt;