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'); );
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.