Trying to create procedure in snowflake and getting error while passing value to another procedure

Question

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

Answer 1

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;
    $$
    ;