x不是函数DATEADD的有效日期/时间组件

Question

这两种语法都可以(带引号和不带引号(

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

现在，我想在这样的存储过程中使用"hour"。我使用了varchar类型：

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';

然而，当我尝试运行该程序时，

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

我得到这个错误：

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.

否则，我如何将小时或日期类型作为参数传递？

Answer 1

使用带绑定的雪花脚本过程。https://docs.snowflake.com/en/sql-reference/stored-procedures-snowflake-scripting.html

像这样：

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();