I am fairly new to snowflake working on a stored procedure having date as a parameter, the same parameter will be used in SELECT statement to work as additional column in result set. Below is the code:
CREATE OR REPLACE PROCEDURE test1 (TEST_DATE VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var prod = TEST_DATE;
var sql_stmt = `select
ID
,Value
,getvariable('PROD') as TestDate
, to_varchar(CURRENT_TIMESTAMP()) CreateDate
from
Table;`;
var statement = snowflake.createStatement( {sqlText:sql_stmt} );
var rs = statement.execute();
rs.next(1);
//return rs.getColumnValue(1);
return rs.getColumnValue(3);
$$;
Calling above procedure as
CALL test1('2022-02-22');
Returning error as "NULL result in a non-nullable column".
Please suggest how can I use the argument as column name in select.
Thank You.
Binding a variable to a SQL statement allows you to use the value of the variable in the statement.
CREATE OR REPLACE PROCEDURE test1 (TEST_DATE VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var sql_stmt = `select
ID
,Value
, :1 as TestDate
, to_varchar(CURRENT_TIMESTAMP()) AS CreateDate
from Table;`;
var statement = snowflake.createStatement( {sqlText:sql_stmt, binds: [TEST_DATE]} );
var rs = statement.execute();
...
$$;
I think Binding like mentioned above is the more proper method, but you could also use string formatting its a bit more readable.
CREATE OR REPLACE PROCEDURE test1 (TEST_DATE VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var prod = TEST_DATE;
var sql_stmt = `select
ID
,Value
,'${TEST_DATE}' as TestDate
, to_varchar(CURRENT_TIMESTAMP()) CreateDate
from
Table;`;
var statement = snowflake.createStatement( {sqlText:sql_stmt} );
var rs = statement.execute();
rs.next(1);
//return rs.getColumnValue(1);
return rs.getColumnValue(3);
$$;