Use parameter in UDF or Stored Procedure to return table

Question

I have a case when using the Snowflake API. We have a bunch of materialized views, multiple for each customer of ours. When using the API, we would like to use functions or stored procedures to produce a result in runtime, and taking the customer ID as a parameter. This parameter would be used to fetch data from the correct views, to avoid have functions for each client. However, I'm running into some issues:

SQL UD(T)F isn't working, since it seems that you can't use a parameter in the FROM clause. Neither can you use variables or running multiple statements if I understood it correctly.
JavaScript UD(T)F isn't working since your not allowed to execute statements.
Stored procedures are working for single values (which is one use case), but not for returning a table. It can return a VARIANT, but that would add work in the service consuming the API which we would like to avoid.

Do you have any suggestions on how to to achieve the result we're after? We could create pre-calculated views with the results we're after, but that would result in a ton of views since we need other parameters as well in the API for a dynamic (filtered) result. Therefore a function-based approach seems much more neat and easier to maintain.

Thanks for your help and support!

Can you use SP to build a table that contains the result so that it can be accessed after the SP is finished? — Eric Lin, Oct 27 '21 at 10:51

score 0 · Answer 1 · answered Oct 27 '21 at 11:49

If the view structure is the same for each client, you can do a UDTF with UNION ALL, merge all views and filter only for the one you need. In practice, you will always read only one view depending on the parameter.

Something like that:

create function t(Client varchar)
returns table(col1 varchar, col2 varchar)
as
$$
      SELECT col1, col2 
        FROM ViewClient1
       WHERE Client = 'Client1'
       UNION ALL
      SELECT col1, col2 
        FROM ViewClient2
       WHERE Client = 'Client2' 
       UNION ALL 
      SELECT col1, col2 
        FROM ViewClient3
       WHERE Client = 'Client3' 
$$;

score 0 · Answer 2 · answered Jan 29 '22 at 23:48

Snowflake Scripting stored procedure could be used.

create or replace procedure test_sp_dynamic(table_name string)
returns table(col varchar, col2 varchar)
language sql
as
$$
declare
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT Y, Z FROM TABLE(?)';
begin
    res := (execute immediate :query using (TABLE_NAME));
    return table(res);
end;
$$;

Test data:

CREATE OR REPLACE TABLE view_1(Y VARCHAR, Z VARCHAR) AS SELECT 1, 2;
CREATE OR REPLACE TABLE view_2(Y VARCHAR, Z VARCHAR) AS SELECT 3, 4 ;

CALL test_sp_dynamic('VIEW_1');
-- 1  2

CALL test_sp_dynamic('VIEW_2');
-- 3  4

Use parameter in UDF or Stored Procedure to return table

2 Answers2