Execute stored procedure inside another stored procedure in snowflake

Question

We created 2 stored procedures separately in snowflake. Now we have master stored procedure which we need to execute. This master stored procedure is written is way that the return value of first stored procedure would trigger second stored procedure.

When I am assigning return value of first stored procedure to variable , it seems like it is not getting assigned.

var CALL_CATALOG_SP_return_val= snowflake.execute({ sqlText: CALL_CATALOG_SP });

How do I ensure that I am second stored procedure runs only when I get particular value from first stored procedure.

Answer 1

Not sure, how you are doing it. I am able to do it as below

CREATE OR REPLACE procedure FIRST_PROC()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
AS
$$
    A = 'DONE';
    return A;
$$;


CREATE OR REPLACE procedure SECOND_PROC()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
AS
$$
    query = 'CALL FIRST_PROC()';
    A= snowflake.execute({ sqlText:query });
    A.next();    
    return_val = A.getColumnValue(1);
    return return_val;
$$;

CALL SECOND_PROC();