how to pass variant data into snowflake table using snowflake stored procedure

Question

may i know how to pass variant data into snowflake table using snowflake stored procedure .

CREATE
OR REPLACE PROCEDURE abc(
    MY_ID STRING,
    P_FILTERS VARIANT
) RETURNS VARIANT 
LANGUAGE JAVASCRIPT as $$
try
{
var P_FILTERS=P_FILTERS;

    var query=" INSERT INTO abc (SQ_ID,id,\  
                                      FILTERS,\
                                      ITERATION)\
        VALUES (abc.nextval,\
                :1,\
                :2,\
                0); "
var sql = snowflake.createStatement( {sqlText: query,binds:[MY_ID,P_FILTERS] });
    var resultSet = sql.execute();
  COMMIT;  
}

catch(error)
{
    return (error);
}
$$;

can some one help and undestand in letting me know

Thanks, Nikhil

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2021-08-02T18:51:19

The solution isn't totally straightforward, but going from variant to string to variant solves the problem:

create or replace temp table variants as 
select 'a'::string id, parse_json('{"hello":"world"}') v
;

CREATE
OR REPLACE PROCEDURE insert_variant(
    MY_ID STRING,
    P_FILTERS VARIANT
) RETURNS VARIANT 
LANGUAGE JAVASCRIPT as $$
var query="INSERT INTO variants (id, v) select :1, parse_json(:2); "
var sql = snowflake.createStatement({
    sqlText: query
    , binds:[MY_ID, JSON.stringify(P_FILTERS)]
});

var resultSet = sql.execute();
$$;

call insert_variant('c', parse_json('{"hello2":"world3"}'))
;

This because

Currently, only JavaScript variables of type number, string, and SfDate can be bound. https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#binding-variables

And trying to parse JSON on the VALUES() part of an insert gives you the error "Invalid expression in VALUES clause". But having an INSERT+SELECT solves it.

how to pass variant data into snowflake table using snowflake stored procedure

1 Answers