Snowflake functions - how to declare variables and use sql statements inside function

0
votes

I need to rewrite a SQL scalar function which is called in power bi reports

how can I achieve the following in snowflake?

create function test (@input varchar(10)) returns varchar ( declare @text varchar(50) declare @cnt int select @cnt = max(key) from tbl;

if @input = 'table1' begin select @text = text from table1 --statements will be different based on input passed and is from tables end

if @input = 'table12' begin select @text = text from table12 where id = @cnt end

...

return @text )

Can you please share some examples where we can declare /set variables and also execute sql statements based on input parameter passed ?

Thanks, Jo

1
snowflake-cloud-data-platformsnowflake-schema

1 Answers

0
votes

I think stored procedure works better in your case. I tried below examples and hope it is what are you after:

create or replace table test (a int);
insert into test values (1), (2);

create or replace table table1 (a varchar, id int);
insert into table1 values ('table1', 1);

create or replace table table2 (a varchar, id int);
insert into table2 values ('table2', 2);
  
CREATE OR REPLACE PROCEDURE my_proc(INPUT VARCHAR) 
RETURNS VARCHAR 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    var stmt = snowflake.createStatement( {sqlText: "SELECT MAX(a) FROM test;"} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    
    var id = resultSet.getColumnValue(1); 
    
    stmt = snowflake.createStatement( {sqlText: "SELECT a FROM " + INPUT + " WHERE id = " + id + ";"} ); 
    resultSet = stmt.execute(); 
    resultSet.next(); 
    
    return resultSet.getColumnValue(1);
$$ ; 

call my_proc('table2');
-- it returns "table2"

I have omitted any validations for simplicity.

Cheers