Save the output of a procedure in snowflake in a variable

I have following procedure:

create or replace procedure todays_delivery_amount(targetkey_variable varchar)
returns number
language sql
as
$$
begin

    if ((SELECT MONTHLY_DELIVERED_AMOUNT FROM test.process.msv_month_amount where TARGET_KEY = '20') = 0)
    then
        return ((SELECT monthly_target_amount from test.process.msv_month_amount)/3) ;
    else
        return ((SELECT monthly_target_amount from test.process.msv_month_amount where TARGET_KEY = '20') - (SELECT MONTHLY_DELIVERED_AMOUNT from test.process.msv_month_amount where TARGET_KEY = '20')) / 
        (SELECT (SELECT DATEDIFF(DAY,CONCAT(LEFT(current_date(), 8), '01')::date, CONCAT(LEFT(current_date(), 8), (SELECT datediff(dd,current_date(),dateadd(mm,1,current_date()))))::date+1) 
                        - DATEDIFF(WEEK,CONCAT(LEFT(current_date(), 8), '01')::date, CONCAT(LEFT(current_date(), 8), (SELECT datediff(dd,current_date(),dateadd(mm,1,current_date()))))::date+1)) - RIGHT(current_date() -1, 2)::number + CAST(Round((day( current_date() ) +6)/7,0) as VARCHAR)::number);
    end if;
end;
$$
;


UNSET todays_amount;

call todays_delivery_amount('10');

Now I want to do two things: First I would like to save the output of the procedure in the variable todays_amount So I tried this:

SET todays_amount = call todays_delivery_amount('10');

But this does not work.

And second: Instead of where TARGET_KEY = '20' i would like to do where TARGET_KEY = targetkey_variable But this does not work.

Answer

It appears that you can’t set it directly from the call statement, but you can do this:

UNSET todays_amount;

call todays_delivery_amount('10');

set todays_amount = (select TODAYS_DELIVERY_AMOUNT from table(result_scan(last_query_id())));