exec macro in stored procedure

[Lesio]

Can an exec macro statement be used within the stored procedure ? The exec macro statement from the code below runs fine out of the procedure in Queryman, while I am getting errors on running create proc when the exec macro is inside the code. The create proc code runs OK, when I take exec macro line out.

REPLACE PROCEDURE OLAPV2_DB.SRF_1 ()

BEGIN

DECLARE LD_SRV_TP BYTEINT DEFAULT 1;
DECLARE LD_USOC_1 CHAR(5) DEFAULT '17CD3';
DECLARE LD_USOC_2 CHAR(5) DEFAULT '17CD4';

DECLARE HCOUNTER BYTEINT DEFAULT -5;
DECLARE PROC_MONTH INTEGER;


WHILE HCOUNTER <= 0

DO

SELECT (ADD_MONTHS(DATE, :HCOUNTER))/100 INTO ROC_MONTH ;

EXEC OLAPV2_DB.SRF_BILL_TRANS_LOAD_1(10304, 1, '17CD3', '17CD4');

SET HCOUNTER = HCOUNTER + 1;

END WHILE;

END;

 

[RogerCruse]

Hi Lesio,
From the Teradata SQL Reference Volume 6 -

[red]Stored procedures do not support the following:
• EXPLAIN and USING modifiers inside a stored procedure
• EXECUTE macro statement[/red]

However, you can do this -

[red]CALL DBC.SYSEXECSQL ('your sql here' || ';');[/red]

So, maybe whatever you want to do within your MACRO can still be achieved directly.

Roger...