Stored Procedure Snowflake with IF statement Null Check

user_17234713 0
Viewed 95

I edited the previous code to make it simpler. Below returns to me always SELECT 2. I expect to have it SELECT 1 because etlId is 0.

Where could the problem be?

Thanks

Regards

'
function getScalarValue(queryString) {
    var out;
    cmd = {sqlText: "set etl_id = 0 "};
    stmt = snowflake.createStatement(cmd);
    var rs;
    rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(1);
    return out;
}
  var etlId = getScalarValue("select $etl_id");
        if (etlId == 0 )  
                {
                    var sql_command = "SELECT 1";
                }
        else {
                    var sql_command = "SELECT 2";
                  }
            try {
            snowflake.execute (
                {sqlText: sql_command}
                );
            return "Success" ;   // Return a success/error indicator.
            }
        catch (err)  {
            return "Failed: " + err;   // Return a success/error indicator.
            }                   
'
snowflake-cloud-data-platform stored-procedures javascript
2 Answers

$etl_id is not a JavaScript variable. It is a SQL variable. You can use a helper function get scalar values from a SQL statement. This helper function gets the first column, first row value and returns it:

Update: Based on your updates, it's more like this:

create or replace procedure TEST()
returns string
language javascript
execute as caller
as
$$

  var etlId = getScalarValue("select $etl_id");
        if (etlId == 0 )  
                {
                    var sql_command = "SELECT 1";
                }
        else {
                    var sql_command = "SELECT 2";
                  }
            try {
            snowflake.execute (
                {sqlText: sql_command}
                );
            return "Success" ;   // Return a success/error indicator.
            }
        catch (err)  {
            return "Failed: " + err;   // Return a success/error indicator.
            }  
            
function getScalarValue(queryString) {
    var out;
    cmd = {sqlText: queryString};
    stmt = snowflake.createStatement(cmd);
    var rs;
    rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(1);
    return out;
}

$$;

set etl_id = 1;
call test();
user_127563810

Thanks for your reply. I have another solution to use a variable as a parameter at the script. When I call the procedure, I can use SQL to define the parameter so this works for me. Once again thank you very much for your help.

Regards

       create or replace procedure TEST(etlId, float)
        returns string
        language javascript
        execute as caller
        as
        $$
                if (etlId == 0 )  
                        {
                            var sql_command = "SELECT 1";
                        }
                else {
                            var sql_command = "SELECT 2";
                          }
                    try {
                    snowflake.execute (
                        {sqlText: sql_command}
                        );
                    return "Success" ;   // Return a success/error indicator.
                    }
                catch (err)  {
                    return "Failed: " + err;   // Return a success/error indicator.
                    }  
      $$
    
call TEST (select count(etl_id) etlId from view where etl_id not in (select etl_id from table))
user_172347130
Related