Snowflake Scripting - How to access a variable that is passed when the stored procedure is called-CodePudding

I am trying to rewrite a Snowflake stored procedure that previously used javascript in snowflake scripting. I am completely new to it. I want to access the value of "my value" from within my block but I can't seem to get it. I'm sure it's something ridiculous, but if someone could help me out I'd really appreciate it.

CREATE OR REPLACE PROCEDURE my_stored_procedure("my_value" varchar)
returns varchar
language sql
as
$$
DECLARE
current_day varchar;
current_month varchar;
current_year varchar;
current_value VARCHAR;
BEGIN

SELECT date_part(day, current_timestamp()) INTO :current_day;                         
SELECT date_part(month, current_timestamp()) INTO :current_month;
SELECT date_part(year, current_timestamp()) INTO :current_year;
current_value=my_value;
RETURN current_value;
exception
  when statement_error then
    return object_construct('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
                            

END;
$$;

CALL my_stored_procedure('value');

As I said I am completely new to snowflake scripting and (if I am honest) my sql is also a little rusty, so any other constructive criticism is also welcomed.

CodePudding user response：

The correct syntax for assignment is :=

current_value=my_value;
=>
current_value:="my_value";

Output:

Also there is no need for " at CREATE OR REPLACE PROCEDURE my_stored_procedure(my_value varchar).