CODEWITHSUNDEEP
javascriptstored-proceduressnowflake-cloud-data-platform
id101112
id101112

Reputation: 1042

How to pass Parameter to Snowflake procedure using JavaScript

I am trying to write a snowflake procedure but I am getting an "invalid identified A"

following is the statement:

create or replace procedure sp_procedureName (A date, B string)
return string Null
language Javascript 
EXECUTE As Caller
AS
$$
with cte as (Select column1, column2 from table1 where colA >= A and colB = B),
cte1 as (Select column1, column2 from table2 where colA >= A and colB = B)
Select column1, column2, column1, column2 from cte1, cte2  //Example select statement

so when I am passing those paramteres my procedure is failing with "invalid identified error"

can someone help me to get the correct way to pass those parameter values to procedure

Upvotes: 1

Views: 2322

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

Here's how to get started. You can reference the Snowflake Stored Procedure API: https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html

Note that the date must be passed using the .toISOString() function because Javascript stringifies Date types into a string that Snowflake does not like. Snowflake is okay with the ISO string version.

create or replace procedure sp_procedureName (A date, B string)
returns string
language javascript 
execute As caller
AS
$$

var sql = `with cte as (Select column1, column2 from table1 where colA >= :1 and colB = :2),
cte1 as (Select column1, column2 from table2 where colA >= :1 and colB = :2)
Select column1, column2, column1, column2 from cte1, cte2  //Example select statement`;

var rs = snowflake.execute({sqlText: sql, binds:[A.toISOString(), B]});

while (rs.next()){

    // Do something for each row here.

}

$$;

call sp_procedureName('2020-01-01', 'Hello world');

Upvotes: 2

Related Questions