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

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

CodePudding user response：

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