Processing aborted due to error Snowflake

Question

I've encountered this error:

Execution error in stored procedure: SQL execution internal error: Processing aborted due to error at Snowflake.execute

when running this script:

CREATE OR REPLACE PROCEDURE DATES_TABLE (INITIALDATE VARCHAR, FINALDATE VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  EXECUTE AS CALLER
AS 
$$
            var DATESDIFF = (Date.parse(formatDate(FINALDATE)) - Date.parse(formatDate(INITIALDATE)))/ (1000 * 3600 * 24);
            
            snowflake.execute(
            
            {
            
                sqlText: `  CREATE OR REPLACE TEMPORARY TABLE TEMP_DATE_RANGE AS SELECT DATE FROM (
                            SELECT
                                CAST(DATEADD (DAY, DatesDiff.n, :1) AS DATE) AS DATE
                            FROM (
                                SELECT
                                    ROW_NUMBER() OVER (ORDER BY 1) - 1
                                FROM
                                    TABLE (generator (rowcount => :3))) DatesDiff (n)
                            ); `,
                            
                    binds: [formatDate(INITIALDATE), formatDate(FINALDATE), DATESDIFF]

                    }
            
            ); 
    
    
        function formatDate(date) {
            var d = new Date(date),
                month = '' + (d.getMonth() + 1),
                day = '' + d.getDate(),
                year = d.getFullYear();

            if (month.length < 2) 
                month = '0' + month;
            if (day.length < 2) 
                day = '0' + day;

            return [year, month, day].join('-');
        }

$$
;

CALL DATES_TABLE('2021-04-01','2021-05-24');

Which when ran outside of the stored procedure, creates a table with dates between the range inputted.

Any idea why this is happening, how to sort it out?

Answer 1

The problem is in binding a variable to TABLE (generator (rowcount => :3)), as Snowflake expects a constant there.

Instead, you could do something like:

 SELECT ROW_NUMBER() OVER (ORDER BY 1) - 1 AS rn
 FROM TABLE (generator (rowcount => 1000))
 QUALIFY rn < :2

I did some cleanup, and this works:

CREATE OR REPLACE PROCEDURE DATES_TABLE (INITIALDATE VARCHAR, FINALDATE VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  EXECUTE AS CALLER
AS 
$$
            var DATESDIFF = (Date.parse(formatDate(FINALDATE)) - Date.parse(formatDate(INITIALDATE)))/ (1000 * 3600 * 24);
            snowflake.execute(          
            {
                sqlText: `
                CREATE OR REPLACE TEMPORARY TABLE TEMP_DATE_RANGE AS
                SELECT CAST(DATEADD(DAY, DatesDiff.rn, :1) AS DATE) AS DATE
                FROM (
                    SELECT ROW_NUMBER() OVER (ORDER BY 1) - 1 AS rn
                    FROM TABLE (generator (rowcount => 1000))
                    QUALIFY rn < :2
                ) 
                ;`
                , binds: [formatDate(INITIALDATE), DATESDIFF]
            }
            
            ); 
    
    
        function formatDate(date) {
            var d = new Date(date),
                month = '' + (d.getMonth() + 1),
                day = '' + d.getDate(),
                year = d.getFullYear();

            if (month.length < 2) 
                month = '0' + month;
            if (day.length < 2) 
                day = '0' + day;

            return [year, month, day].join('-');
        }

$$
;

CALL DATES_TABLE('2021-04-01','2021-05-24');
select * from TEMP_DATE_RANGE;

For a shorter way of generating a sequence of dates, see my answer to https://stackoverflow.com/a/66449068/132438.