Below procedure to get the value from the metadata table and pass its value into another procedure.
create or replace procedure get_results() RETURNS VARIANT LANGUAGE javascript as $$ var return_value = []; var SQL_STMT = "select * from metad" ; var stmt = snowflake.createStatement( { sqlText: SQL_STMT } ); /* Creates result set */ var res = stmt.execute(); while (res.next()) { //return_value += "n"; var srctbl=res.getColumnValue('SRC_TBL'); var tgttbl=res.getColumnValue('TGT_TBL'); var prc_stmt = 'CALL sync_table('+ srctbl + ',' + tgttbl+')'; var pstmt = snowflake.createStatement({sqlText: prc_stmt}); /* Creates result set */ var spResult = pstmt.execute(); if(spResult !='Success'){ return_value=spResult; break; } } return return_value; $$ ;
CALL get_results()
Give the error message:
Execution error in store procedure GET_RESULTS: SQL compilation error: error line 1 at position 16 invalid indetifier ‘STBL1’ At Statement.execute, line 18 position 26
Advertisement
Answer
I am going to guess that both the values you are passing to the Stored Procedure sync_table
are strings, and you sql command prc_stmt
will look like:
CALL sync_table( soruce_table_name, target_table_name );
but that is not valid SQL, it should be
CALL sync_table( 'soruce_table_name', 'target_table_name' );
which means you should add some quotes, given this is javascript the string can use double quotes and allow simple insertion of the needed single quotes:
create or replace procedure get_results() RETURNS VARIANT LANGUAGE javascript as $$ var return_value = []; var SQL_STMT = "select * from metad" ; var stmt = snowflake.createStatement( { sqlText: SQL_STMT } ); /* Creates result set */ var res = stmt.execute(); while (res.next()) { //return_value += "n"; var srctbl=res.getColumnValue('SRC_TBL'); var tgttbl=res.getColumnValue('TGT_TBL'); var prc_stmt = "CALL sync_table('"+ srctbl + "','" + tgttbl+"')"; var pstmt = snowflake.createStatement({sqlText: prc_stmt}); /* Creates result set */ var spResult = pstmt.execute(); if(spResult !='Success'){ return_value=spResult; break; } } return return_value; $$ ;