Calling multiple stored procedures from stored procedure

Database

Calling multiple stored procedures from stored procedure

I am using a master table (contains one row for each SP) and a master procedure to execute multiple stored procedures.

CREATE master_proc()

BEGIN

FOR ... AS SELECT sp_name FROM master_table

DO

EXECUTE IMMEDIATE ' CALL ' || sp_name || '();'     ; the result would be CALL sp_name();

END FOR;

END;

but calling master SP: CALL master_proc(); it returns an error

call failed [7689] invalid dynamic SQL statement

Is it the right syntax? Am I missing something?

Also, is it possible to use a Wait command between SPs to make sure next sp won't start until previous one is done?

thank you!

3 REPLIES
Enthusiast

Re: Calling multiple stored procedures from stored procedure

From where do you create procedure?

Re: Calling multiple stored procedures from stored procedure

I created SPs from SQL Assistant but I'm not creating any stored procedure within dynamic script; I am just trying to call them.

Enthusiast

Re: Calling multiple stored procedures from stored procedure

I run following script:

DATABASE SAMPLES;
CREATE TABLE master(x VARCHAR(20));
REPLACE PROCEDURE master_proc()
BEGIN
FOR rec AS Cur CURSOR FOR SELECT x FROM master DO
EXECUTE IMMEDIATE ' CALL ' || rec.x || '(); ';
END FOR;
END;

but get mistake:

SPL1027:E(L4), Missing/Invalid SQL statement'E(3706):Syntax error: EXECUTE not allowed.'.

SPL1027:E(L4), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like a name or a Unicode delimited identifier between the 'IMMEDIATE' keyword and a string or a Unicode character literal.'.

How do you create master procedure?