Stored Procedure with Date parameter failed with error: “Unsupported type for binding argument 2”
It is common that we will need to perform Date transformations inside Stored Procedures in SQL. Recently I have encountered an error that Snowflake does not like the format that is generated by Javascript inside Stored Procedure code, and resulted an error. See below example:
CREATE OR REPLACE PROCEDURE test_sp (MYDATE DATE) RETURNS VARCHAR(255) LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS $$ var result = snowflake.execute( { sqlText: 'SELECT COALESCE(:1, NULL)', binds: [MYDATE] } ); result.next(); return result.getColumnValue(1) $$;
To execute this Stored Procedure, simply run below query:
CALL test_sp(CURRENT_DATE());
And we can see it will fail with below error:
Execution error in store procedure TEST_SP: Invalid binds argumentMon Dec 07 2020 00:00:00 GMT+0000 (UTC). Error: Unsupported type for binding argument 2undefined At Snowflake.execute, line 2 position 23
We can see that Snowflake tried to pass value “Mon Dec 07 2020 00:00:00 GMT+0000 (UTC)
” to the bind variable, and resulted in error.
After researching, I found out that Javascript’s Date.toString() function returns non-standard date string format, like the one we see above “Mon Dec 07 2020 00:00:00 GMT+0000 (UTC)
“. It was mentioned in the StackOverflow post here: Convert date into sfdate for snowflake. And it also provided a solution, which is to convert the format to the one that Snowflake recognizes. See below the correct version of code:
CREATE OR REPLACE PROCEDURE test_sp (MYDATE DATE) RETURNS VARCHAR(255) LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS $$ var result = snowflake.execute( { sqlText: 'SELECT COALESCE(:1, NULL)', binds: [MYDATE.toISOString()] } ); result.next(); return result.getColumnValue(1) $$;
The change is basically update “binds: [MYDATE]
” to “binds: [MYDATE.toISOString()]
“. Then the Stored Procedure can execute correctly:
CALL test_sp(CURRENT_DATE()); +--------------------------+ | TEST_SP | |--------------------------| | 2020-12-07T00:00:00.000Z | +--------------------------+
Hope this information can be useful.