Teredata Error 5497: CALL cannot be submitted in multi-statement request

Database
WAQ
N/A

Teredata Error 5497: CALL cannot be submitted in multi-statement request

Hi,

I have a macro in which I have two statements. One is the SP call and the other one is a SELECT statement. The body of both SP and macro is given below:

REPLACE PROCEDURE WAQ.DATA_VALIDATION(
pCOLN_NAME VARCHAR(500)
, pPROVIDED_VAL VARCHAR(500)
)

BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
RESIGNAL;
END;
SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = ' provided value is ';

END;

REPLACE MACRO  WAQ.create_load_script (Process_Name (VARCHAR(500)), Collect_Stats (VARCHAR(500)), Verification_Flag (VARCHAR(500)) )
AS (
CALL WAQ.DATA_VALIDATION('Collect_Stats', :Collect_Stats)
;
SELECT CURRENT_TIMESTAMP
;
)
;

I am calling macro using the following statement in SQLA:

EXEC WAQ.create_load_script('WAQ', 'P', '1');

When I execute the macro using above statement I get an error "Teredata Error 5497: CALL cannot be submitted in multi-statement request"

However, If I keep only one statement in macro (by either removing SP or SELECT call) it works fine without any error. I have tried different positions of semi colon in the macro including

CALL
;SELECT;

but of no use and still getting the same error from both SQLA and BTEQ.

Any help would be highly appreciated.

2 REPLIES
Teradata Employee

Re: Teredata Error 5497: CALL cannot be submitted in multi-statement request

The body of a macro is executed as a single request.

If the macro contains more than one statement, it is a multi-statement request.

WAQ
N/A

Re: Teredata Error 5497: CALL cannot be submitted in multi-statement request

Thanks Fred for your response. So Can't we do it the way I am trying to do?

Macros can have more than one SQL statement but can't we add a SP call with the SQL or any other statement in the macro?