Parameterized snowflake sql stored procedure - snowflake scripting

Question

I have a use case that I need to run a sql code snippet in a stored procedure, I saw this post and I didn't see how I can utilize my code using the suggested solution.

My stored procedure is static, meaning, I address the table X directly and I want it be a parameter that will be provided to stored procedure

create or replace procedure general_wait_proc ()
returns string
language sql as

declare last_time timestamp;
        stop number;
        interval number := 5;
        max_timeout number := 10;
begin
  select max(ts) into :last_time from X;
  let counter := current_timestamp()::timestamp;
  let next_time := current_timestamp()::timestamp;
     while (last_time < counter) do
        call system$wait(:interval);
        select max(ts) into :last_time from X;
        set next_time := dateadd('second', :interval, next_time);
        set stop := datediff('second', counter, next_time);
          if (stop > :max_timeout) then
             return 'Timeout: ' || stop || ' seconds';
             break;
         end if;
     end while;
 return 'Check started at: ' || counter || ', Table last updated at: '|| last_time || ', Total wait time: ' || datediff('second',counter,last_time ) || ' Seconds';
 end;

What I want to do is to pass in arguments to the stored procedure (it will be the table name that I want to query (in my case the X table)

Basically what I'm looking for is a way to do something like this:

create or replace procedure general_wait_proc (table_name string)
returns string
language sql as

declare last_time timestamp;
    stop number;
    interval number := 5;
    max_timeout number := 10;
 begin
  select max(ts) into :last_time from **table_name**;
  let counter := current_timestamp()::timestamp;
  let next_time := current_timestamp()::timestamp;
    while (last_time < counter) do
    call system$wait(:interval);
    select max(ts) into :last_time from **table_name**;
    set next_time := dateadd('second', :interval, next_time);
    set stop := datediff('second', counter, next_time);
      if (stop > :max_timeout) then
         return 'Timeout: ' || stop || ' seconds';
         break;
     end if;
 end while;
 return 'Check started at: ' || counter || ', Table last updated at: '|| last_time || ', Total wait time: ' || datediff('second',counter,last_time ) || ' Seconds';
end;

I know I need to use the EXECUTE IMMEDIATE but I have trouble to store the output of the query that I execute in a variable using that. Any pointers on how to achieve that?

Answer 1

I used Pankaj as a reference to build my use-case and this is what I've came up with:

create or replace procedure general_wait_proc (table_name string , date_column string, interval number, max_timeout number)
returns string
language sql as

 declare
    last_time timestamp;
    start_sensor timestamp default current_timestamp()::timestamp;
    next_time timestamp default current_timestamp()::timestamp;
    stop number;
    query_str string;
    rs resultset default (select current_timestamp()::timestamp);
    c1 cursor for rs;
 begin 
   query_str := 'select max('|| :date_column || ') as ts from '|| :table_name;
          rs := (execute immediate :query_str);
       open c1 for rs;
       fetch c1 into last_time;
          while (:last_time < :start_sensor) do
              call system$wait(:interval);
                rs := (execute immediate :query_str);
                open c1 for rs;
                fetch c1 into last_time;
                   next_time := dateadd('second', :interval, :next_time);
                        stop := datediff('second', start_sensor, :next_time);
                   if (:stop >= :max_timeout) then
                      return 'Table '||upper(table_name)|| ' last updated at: '|| last_time ||' Timeout: ' || stop || ' seconds';
                      break;
                   end if;
         end while;
  return 'Check started at: ' || start_sensor || 
    ', Table '||upper(table_name) || 
    ' last updated at: '|| last_time || 
    ', Total wait time: ' || datediff('second',start_sensor,last_time ) || ' Seconds';
 end;

called it

call general_wait_proc ('my_table' ,'ts',5,60);

Answer 2

Table/ Data used -

select * from temp_tab_split_members;
+--------+-----------+------------+
| SEQ_ID | MEMBER_ID | NAME       |
|--------+-----------+------------|
|      0 |         1 | my name-0  |
|      1 |         2 | my name-1  |
|      2 |         3 | my name-2  |
|      3 |         4 | my name-3  |
|      4 |         1 | my name-4  |
|      5 |         2 | my name-5  |
|      6 |         3 | my name-6  |
|      7 |         4 | my name-7  |
|      8 |         1 | my name-8  |
|      9 |         2 | my name-9  |
|     10 |         3 | my name-10 |
|     11 |         4 | my name-11 |
+--------+-----------+------------+

Dynamic procedure code accepting table name as input

create or replace procedure general_wait_proc (table_name string)
returns integer
language sql as
$$
declare
ret_mem_id integer:=100000;
query_str varchar2(100);
rs resultset default (select 0);
c1 cursor for rs;
begin
  query_str := 'select max(member_id) as member_id from '|| :table_name;
  rs := (execute immediate :query_str);
  open c1 for rs;
  fetch c1 into ret_mem_id;
  return ret_mem_id;
end;
$$
;
+--------------------------------------------------+
| status                                           |
|--------------------------------------------------|
| Function GENERAL_WAIT_PROC successfully created. |
+--------------------------------------------------+

Procedure call statement and result -

call general_wait_proc('temp_tab_split_members');
+-------------------+
| GENERAL_WAIT_PROC |
|-------------------|
|                 4 |
+-------------------+

Please refer here for more details on working with cursors.