Error during Insert JSON object in variant data type -snowflake stored procedure

Question

I'm facing an error when inserting a JSON object into a VARIANT data type column, from within a stored procedure in Snowflake.

Below is the reference code:

create or replace procedure test1234()
returns varchar not null
language javascript
as
$$
       var birthDateVar = 'BIRTH_DATE'; 
       var genderVar = 'GENDER';
       var countryVar = 'COUNTRY';
       var loanVar = 'LOAN_AMOUNT'; 
       var emailVar = 'EMAIL';
       var tableName = 'SF_STRUCT_STAGE_RAW';
       var UPDATE_DATE = "2019-01-01"; 
       var email = "[email protected]";
       var row_num = "1";
       var person_id = "1";
       var EMAILERROR = {
        "row_num": row_num,
        "person_id": person_id,  
        "tableName": tableName,
        "fieldName": emailVar,
        "fieldValue": email,
        "errorDesc":  'value is invalid email'
       };

       var cmd = "insert into error_details_log values(:1,:2);";
       var stmt = snowflake.createStatement(
        {
            sqlText: cmd,
            binds: [EMAILERROR, UPDATE_DATE]
        }
        );
       stmt.execute();
       return emailError;
$$;

Answer 1

Inserting Object types via binding variables directly is not currently supported from within Stored Procedures. Quoting the relevant portion from the Snowflake documentation:

When you bind JavaScript variables to SQL statements, Snowflake converts from the JavaScript data types to the SQL data types. You can bind variables of the following JavaScript data types:

• number

• string

• SfDate

However, the example in the documentation for TIMESTAMP datatypes involving use of strings and recasting can be adapted for your use-case, using the PARSE_JSON(…) function and JSON.stringify(…) JavaScript API:

[…]
// Convert Object -> String
var EMAILERROR_str = JSON.stringify(EMAILERROR);

// Ask Snowflake to parse String as JSON (String -> Object)
var cmd = "insert into error_details_log select PARSE_JSON(:1), :2;";

var stmt = snowflake.createStatement(
  {
    sqlText: cmd,

    // Bind the String value, not the Object one
    binds: [EMAILERROR_str, UPDATE_DATE]

  }
);
[…]