Print
All Forums Database
apolodor 4 posts Joined 04/12
29 Apr 2012
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!

Angellore 16 posts Joined 06/10
30 Apr 2012

From where do you create procedure?

apolodor 4 posts Joined 04/12
30 Apr 2012

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

Angellore 16 posts Joined 06/10
30 Apr 2012

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?
 

You must sign in to leave a comment.