CODEWITHSUNDEEP
javascriptarraysbindingsnowflake-cloud-data-platform
UMinky
UMinky

Reputation: 21

Snowflake: Procedure with ARRAY parameter called from another procedure

I would like to bind an array to a call in a procedure.

I get the following error message:

Execution error in stored procedure TEST_CALL_DO_WITH_ARR: Invalid binds argumentGabi,Klaus,Sabine.
Error: Unsupported type for binding argument 2undefined At Snowflake.execute, line 2 position 25

CREATE OR REPLACE PROCEDURE Test_DO_WITH_ARR(ARR ARRAY )
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    // Do something with the Array
    return 'OK' ;
$$
;


CREATE OR REPLACE PROCEDURE Test_CALL_DO_WITH_ARR()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    var Param_ARRAY =  ['Gabi','Klaus','Sabine'] ;
    var stmt = snowflake.execute({ sqlText: "CALL Test_DO_WITH_ARR(:1)", binds: [Param_ARRAY] });
    return stmt ;
$$
;

CALL Test_CALL_DO_WITH_ARR() ;

Upvotes: 0

Views: 2033

Answers (1)

Eric Lin
Eric Lin

Reputation: 1530

Currently, Snowflake Stored Procedure in JS only supports number, string and SfDate as bind variables, as stated here in our documentation:

https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#binding-variables

Currently, only JavaScript variables of type number, string, and SfDate can be bound.

The workaround would be to pass as String and convert them into Array using JS in the inner function call.

Upvotes: 1

Related Questions