passing Sp parameter in the body at runtime in snowflake

Question

I have created a stored procedure to accept 2 parameters which helps to determine load type and based on that I am updating the control table to change dates.

When I am calling the SP it is failing due to:

Unexpected identifier in USP_NIGHTLYJOBRESETDAYS at 
' ResetDaysDateRange = SELECT (`DATEADD(day,-?,DATEADD(day,DATEDIFF(day, '1900-01-01'::date, CURRENT_TIMESTAMP::date),'1900-01-01'::date))`,binds:[RESETDAYS]);' position 33

Here is the stored procedure code

    CREATE OR REPLACE PROCEDURE etl.usp_NightlyJobResetDays (NIGHTLYLOAD VARCHAR(10), RESETDAYS VARCHAR(10))
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
      var sql_command = 
    `BEGIN
    
       let ResetDaysDateRange;

       //Capturing date based on value

       ResetDaysDateRange = SELECT (`DATEADD(day,-?,DATEADD(day,DATEDIFF(day, '1900-01-01'::date, CURRENT_TIMESTAMP::date),'1900-01-01'::date))`,binds:[RESETDAYS]);
       
        //checking load type
       if (NIGHTLYLOAD ='Yes')
       {
                EXEC(`Update Reporting.ReportingLoadDetail     
                SET MaxLoadDate = ?
                WHERE IsNightlyLoadImpacted <> 'Yes'`,[ResetDaysDateRange]); 
                
                
                EXEC(`UPDATE Reporting.ReportingLoadDetail
                SET MaxLoadDate =  CASE WHEN ? > LastInitialLoadDate THEN ? 
                                        ELSE LastInitialLoadDate 
                                   END 
                WHERE IsNightlyLoadImpacted = 'Yes'`,[ResetDaysDateRange,ResetDaysDateRange]);
                
      } 
    
    //reset restartabilityStatus to completed if last incremental load got failed
        EXEC(`UPDATE etl.APILastLoadDetail set RestartabilityStatus = 'Completed'`);
        
        END`
     try { 
        snowflake.execute (
          {sqlText: sql_command}
          );
        return "Succeeded.";  // Return a success/error indicator.
             
        }
      catch (err) {
        return "Failed: " + err;  // Return a success/error indicator.
        }
    
    $$
    ;
    



//Calling sp
call  etl.usp_NightlyJobResetDays('Yes',30);

T_Krueger1988 · Answer

something like this should work:

    CREATE OR REPLACE PROCEDURE ETL.usp_NightlyJobResetDays (NIGHTLYLOAD VARCHAR(10), RESETDAYS VARCHAR(10))
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    try{
      //Capturing date based on value
      var sql_command1 = `SELECT TO_CHAR((DATEADD(day,-`+RESETDAYS+`,DATEADD(day,DATEDIFF(day, '1900-01-01'::date, CURRENT_TIMESTAMP::date),'1900-01-01'::date))))`;
      var ResetDaysDateRange_res = snowflake.execute ({sqlText: sql_command1});
      ResetDaysDateRange_res.next()
      var ResetDaysDateRange = ResetDaysDateRange_res.getColumnValue(1);
      
        //checking load type
       if (NIGHTLYLOAD == `Yes`)
       {
                var sql_command2 = `Update Reporting.ReportingLoadDetail     
                                    SET MaxLoadDate = TO_DATE('`+ResetDaysDateRange+`')
                                    WHERE IsNightlyLoadImpacted <> 'Yes'`;
                
                snowflake.execute ({sqlText: sql_command2});
                
                var sql_command3 = `UPDATE Reporting.ReportingLoadDetail
                                    SET MaxLoadDate =  CASE WHEN TO_DATE('`+ResetDaysDateRange+`') > LastInitialLoadDate THEN TO_DATE('`+ResetDaysDateRange+`') 
                                        ELSE LastInitialLoadDate 
                                    END 
                                    WHERE IsNightlyLoadImpacted = 'Yes'`;
                                    
                snowflake.execute ({sqlText: sql_command3});                    
                
      } 
    
    //reset restartabilityStatus to completed if last incremental load got failed
    var sql_command4 = `UPDATE etl.APILastLoadDetail set RestartabilityStatus = 'Completed'`;
    snowflake.execute ({sqlText: sql_command4});
    
        return "Success";
    
    }

    catch (err) {
        return "Failed: " + err;  // Return a success/error indicator.
    }   
$$
;

Best regards,

TK

passing Sp parameter in the body at runtime in snowflake

1 Answers