Passing a variable in Snowflake Procedure

Question

i am running a query inside the procedure(using Javascript) and passing a variable to where condition , getting error. please correct me here.

var v='col1' select * from table1 t where t.v='string'

in above query i am using variable in where condition instead of column name. how do we pass a variable here(t.v)

Thanks

Can you share more code from the procedure? That line doesn't seem like valid code to me — Felipe Hoffa
due to security reasons i am not able to share the code but code looks like below. — BalajiAWS
due to security reasons i am not able to share the code but code looks like below. var v='col1' var sql_stmt=snowflake.execute({sqlText: "select * from table1 t1 where t1.${v}='string' ; " here when i am passing variable in where condition i.e t1.v, it is not taking , throwing below error compilation error , error code: 100183 unexpected t1.v — BalajiAWS

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-03-23T08:38:45

You need to use backquote (`) to be able to use template strings:

create table table1 ( col1 varchar, col2 varchar);
insert into table1 values ('string','Gokhan'), ('int','1');

create or replace procedure test_sp( v varchar)
returns string  
language javascript
as
$$
var v=V; // get the param
var result=snowflake.execute({sqlText: `select * from table1 t1 where t1.${v}='string'; ` } );
result.next();
return result.getColumnValue(2);
$$;

call test_sp('col1');

+---------+
| TEST_SP |
+---------+
| Gokhan  |
+---------+

Based on the comments:

CREATE OR REPLACE PROCEDURE SP_TEMP(IN_V VARCHAR) RETURNS VARCHAR(100) LANGUAGE JAVASCRIPT AS  
$$ var v_brand ='brand'; 
var v1='col1' ;
var ALERT_CURSOR=snowflake.execute({sqlText: "SELECT col1,col2 FROM CDW_US_DIMS_DB.FCT_ALERTS_PARAMS_OBR WHERE col1='"+IN_V+"' "});  
while(ALERT_CURSOR.next()) { var col1=ALERT_CURSOR.getColumnValue(1); var col2=ALERT_CURSOR.getColumnValue(2);
   if (v_brand == 'v_'+col4.trim()) { v_brand=col2; } else {v_brand=col3;}
v_WORK_SQL_ALRT_VOL1 = snowflake.execute({sqlText: "create temporary table CDW_DB.TEMP1(col1 INTEGER,col2 VARCHAR(255),RNK INTEGER);"}) 
v_WORK_SQL_ALRT_VOL2 = snowflake.execute({sqlText: `insert into CDW_DB.TEMP1 select DISTINCT  FCT.COL1,FCT.COL2 , RANK() OVER (PARTITION BY FCT.COL1 ORDER BY FCT.COL2 ASC) AS RNK from CDW_DB.FCT_TBL FCT WHERE COL1 <> -1 and FCT.${v_brand} QUALIFY  RNK=1;`}); 
var v_DROP_VOL_TBL1 =snowflake.execute({sqltext:"DROP TABLE CDW_DB.TEMP1"}); } return v_brand; 
$$;

Passing a variable in Snowflake Procedure

1 Answers