Is is possible to execute a macro via dynamic SQL?


Is is possible to execute a macro via dynamic SQL?

I'm seeking some guidance using dynamic sql to invoke a macro. Specifically: is this possible?

As part of a proof of concept, I've created a very simple test case with one macro (tst_prnt_mthd_mac) and one stored procedure which dynamically calls it (tst_run_sp). The macro simply inserts a row into a table to let me know it ran.

CREATE MACRO tst_print_mthd_mac AS
( insert into debug (tm, vname, detail) VALUES (current_timestamp, 'mthd name', 'test method invoked');

CREATE PROCEDURE tst_run_sp ()
DECLARE mthdlgy_exec_nm varchar(55) default 'tst_print_mthd_mac';
-- CALL DBC.sysExecSQL ('exec ' || :mthdlgy_exec_nm);
CALL DBC.sysExecSQL ('exec ' || 'tst_print_mthd_mac');

Both versions of the CALL statement yield the error "5568: tst_run_sp: SQL statement is not supported within a stored procedure". Is this because the macro is considered a mutli-statement request? If that's the case, are there any other recommended options?

In case more context is needed, I'm working on a proof of concept for a prototype that will ultimately (hopefully) run 'models' that consist of 'methodologies' which can be used across many models. A methodology will be either a macro or stored procedure that writes to an output table. I would like to be able to call a stored procedure with the model name, get the associated methodologies, and then call the appropriate macros/procedures to execute all methodologies for the model.

Thanks for any insight you can provide.

Re: Is is possible to execute a macro via dynamic SQL?

We can not Execute the macro using DBC.sysExecSQL. It is not allowed.
Anyway we can not execute the macro in stored procedure.