x is not a valid date/time component for function DATEADD-CodePudding

Both of these syntaxes work (with and without the quotes)

SELECT DATEADD(hour, -1, CURRENT_TIMESTAMP), DATEADD('hour', -1, CURRENT_TIMESTAMP)

Now, I want to to use 'hour' in a stored procedure like this. I used the varchar type:

CREATE OR REPLACE PROCEDURE "EXECUTE_INSERT_TEST"(hour_or_date varchar, Load_day_number number, load_day_from timestamp)
RETURNS NUMBER(38,0)
LANGUAGE SQL
EXECUTE AS OWNER
AS 'begin
INSERT INTO TABLE_1
(FILE_NAME, LOAD_DATE)
SELECT FILE_NAME, LOAD_DATE
FROM TABLE_2
     where 1 = 1  
       and LOAD_DATE >= DATEADD(hour_or_date, -1, CURRENT_TIMESTAMP)
;
  RETURN 1;
end';

However, when I try to run the procedure,

CALL "EXECUTE_INSERT_TEST"('hour', -1, CURRENT_TIMESTAMP)

I get this error:

SQL Error [2151] [22023]: Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 0 : SQL compilation error: ['HOUR_OR_DATE'] is not a valid date/time component for function DATEADD.

How else can I pass the hour or date types as parameters?

CodePudding user response：

Use snowflake scripted procedure with binding. https://docs.snowflake.com/en/sql-reference/stored-procedures-snowflake-scripting.html

like this :

CREATE OR REPLACE PROCEDURE TEMP."EXECUTE_INSERT_TEST"(hour_or_date varchar, Load_day_number number, load_day_from timestamp)
RETURNS NUMBER(38,0)
LANGUAGE SQL
EXECUTE AS OWNER
AS 
$$
BEGIN
    INSERT INTO TABLE_1(FILE_NAME, LOAD_DATE)
    SELECT FILE_NAME, LOAD_DATE
    FROM TABLE_2
    where 1 = 1  
      and LOAD_DATE >= DATEADD(:hour_or_date, -1, CURRENT_TIMESTAMP);   
    RETURN 1;  
 END;
$$

CALL "EXECUTE_INSERT_TEST"('hour', -1, CURRENT_TIMESTAMP);

CREATE OR REPLACE TABLE TEMP.table_2 ( file_name varchar(55), load_date timestamp);
CREATE OR REPLACE TABLE TEMP.table_1 ( file_name varchar(55), load_date timestamp);

INSERT INTO table_2
SELECT 'some_file',current_timestamp();