Call stored procedure using parameter as array in Snowflake

Question

I am trying to call a stored procedure with in a stored procedure using SQL language.

call Parent_Proc(varchar, varchar, array)

var 1 = PROCESS_TBL
Var 2 = AUDIT_TBL
Var 3 = [PROCESS_NM1, PROCESS_NM2, PROCESS_NM3]

while loop:
call child_proc(var 1,var 2,var 3[1] )

return process_nm +'response from child proc'

I am trying to apply while loop but getting error, also need to return success/failure status for each process_nm in the end.

My code :

CREATE OR REPLACE PROCEDURE Parent_Proc(process_tbl varchar ,audit_tbl varchar,process_nm array)       
returns varchar
as $$ 
declare
COUNTER int default 1;
begin
  while (COUNTER <= process_name.length) do
    call Child_Proc(process_tbl ,audit_tbl,process_nm[COUNTER-1]);
    COUNTER := COUNTER + 1;
    return 
   end while;

end;

$$;

Getting error invalid Identifier process_nm

Answer 1

You need to use ARRAY_SIZE instead of process_name.length:

https://docs.snowflake.com/en/sql-reference/functions/array_size

CREATE OR REPLACE PROCEDURE Parent_Proc(process_tbl varchar ,audit_tbl varchar,process_nm array)       
returns varchar
as  
declare
  COUNTER int default 1;
  tmp varchar;
begin
  while (COUNTER <= ARRAY_SIZE(process_nm) ) do
    tmp := process_nm[COUNTER-1] ;
    call Child_Proc( :process_tbl ,:audit_tbl, :tmp );
    COUNTER := COUNTER + 1;
    -- return 'XYZ'
   end while;
end;