Parent Stored procedure call

Database
Enthusiast

Parent Stored procedure call

Hi All, 

 

I created a child stored procedure with 4 input parameters varchar 3 fields and one of the date field which is a variable in all the procedures. This is running fine individually. However , when I created a parent procedure and calling this child procedure, i get error. 

 

I am wanting to create a parent procedure with these 4 parameters and calling the child procedure here. 

 

Replace proc Proc name ( in sp_name varchar (30), in tbl_name varchar (30), IN db_name varchar(30), IN load_start_date_time Varchar(19))

 

begin 

call sp name ( '||sp_name||','||tbl_name||','||db_name||','||Load_Start_date_Time||')

end; 

 

I am calling the parent stored procedure with values CALL proc name (sp_name,tbl_name,db_name,load_start_date_time) 

 

I tried the load_start_date_time as current_timestamp(0). I get errors. 

 

I keep getting error. 

CALL Failed 5531- Named list is not supported for arguments of a procedure. 

 

Any help is appreciated. 

 

Thanks

4 REPLIES 4
Teradata Employee

Re: Parent Stored procedure call

Why the quotes and concatenation?

call child_sp (:sp_name,:tbl_name,:db_name,:Load_Start_date_Time);

If you have multiple "child" procedures and are trying to pass an SP name to the "parent" to dynamically build a CALL statement, that's not allowed.

Enthusiast

Re: Parent Stored procedure call

I tried this- 

 

call child_sp (:sp_name,:tbl_name,:db_name,:Load_Start_date_Time);

Idea is to add multiple child procedures in future for audit purposes. We have multiple procedures which has business logic and want to pass that value so that It runs and gives the output. 

Load start date time is a variable which I am passing in all the procedures. Calling the audit procedure after each procedure runs. 

 

Load start date time is a varchar and converted it to timestamp in the select. child procedure works separately . When  I include it in parent, i keep getting errors. 

 

Teradata Employee

Re: Parent Stored procedure call

What is the error?

If the child expects a TIMESTAMP then add explicit CAST or TO_TIMESTAMP..

call child_sp (:sp_name,:tbl_name,:db_name,CAST(:Load_Start_date_Time as TIMESTAMP(0)));

If you want the parent SP to conditionally call child_sp1 or child_sp2 or ... you will have to code a separate CALL statement for each child with CASE or IF/THEN logic to test the value passed and choose which of those CALL statements to execute. 

Enthusiast

Re: Parent Stored procedure call

It worked! Thank you Fred. I may have copied and later I typed manually.