Snowflake call a stored procedure inside a loop N times-CodePudding

I have a snowflake sproc that runs on 4 hours of data at a time and outputs entries for the first hour.

It has 3 params i.e start_hour, end_hour and output table name.

I want to run this for a full day in a loop and I am trying to write the following looped procedure:

create procedure abc_daily()
returns varchar
language sql
as
$$
    declare
        iteration_limit integer default 24;
        counter integer default 0;
    begin
        for i in 0 to iteration_limit do
              
            call abc(
            date_trunc('hour', dateadd(hour, counter, convert_timezone('UTC', '2022-05-10  00:00:00.000  0000'))),
            date_trunc('hour', dateadd(hour, counter, convert_timezone('UTC', '2022-05-10  04:00:00.000  0000'))),
           'output table name'

            
            counter := counter   1;
        end for;
    end;
$$;

The date_add will help slide my window till 23rd hour.

However, when I run this create command, I get

Syntax error: unexpected 'abc'. (line 19)

Looks like it does not recognize the call command.

Could anyone help get this running ?

Do I need to use snowflake.execute here?

CodePudding user response：

You call does not have a closing parent and semicolon, and your times are not recognized

try:

create procedure abc_daily()
returns varchar
language sql
as
$$
declare
    iteration_limit integer default 24;
    counter integer default 0;
begin
    for i in 0 to iteration_limit do
           
        call abc(
            date_trunc('hour', dateadd(hour, counter, convert_timezone('UTC', '2022-05-10  00:00:00.000'))),
            date_trunc('hour', dateadd(hour, counter, convert_timezone('UTC', '2022-05-10  04:00:00.000'))),
           'output table name');
            
        counter := counter   1;
    end for;
end;
$$;

or put the timestamp into a variable, and just reuse it:

create procedure abc(f timestamp_ntz, t timestamp_ntz, s text)
returns text
language sql
as
$$
begin
    return f::text || t::text || s;
end;
$$;

create or replace procedure abc_daily()
returns varchar
language sql
as
$$
declare
    iteration_limit integer default 2;
    start_time timestamp_ntz;
begin
    start_time := date_trunc('hour', to_timestamp_ntz('2022-05-10 00:00:00.000'));
    
    for i in 0 to iteration_limit do

        call abc( dateadd(hour, :i, :start_time), dateadd(hour, :i 4, :start_time), 'output table name');
    end for;
end;
$$;


call abc_daily();