Use of Variable in Snowflake Stored Procedure

user_16629263 0
Viewed 1562

I have to add a variable MaxDate in my SQL Stored Proc (shown below). The code gets errored out since MaxDate is not represented by its value. Any idea on how I can pass a variable in a stored proc?

create or replace procedure Load_Employee()
  returns varchar not null
  language javascript
  EXECUTE AS CALLER
  

  as
  $$
  //Variable Initialization
    
    var IntegrationTable ='EMPLOYEE';
    var TypeID=0;
    var MaxDate=' ';
    var cmd = "Select max(COMPLETED_DATE) from SCHEMA.TABLE where TARGET_TABLE_NAME=  " + "'" + IntegrationTable + "'"  ;
  
    var sql = snowflake.createStatement({sqlText: cmd});
    var result = sql.execute();
    result.next();
 
   MaxDate=result.getColumnValue(1);
   var cmd=` Insert into PersonTable   

    select SHA1(concat(Person_id,'|','Person')) ,12345678,SHA1(concat('Payroll','|','Pay','|', Load_Date)) ,current_timestamp() , Tenant
    from Schema.PERSONTABLE where Date_Added >= MaxDate

    where  TYPE='ABC' ;`;


  
  $$
  ;

snowflake-cloud-data-platform
1 Answers

If your query to get MaxDate works right, then the value should be in the variable. The problem is it's not being replaced in the sql variable defining the insert statement.

Since you're using backticks to open and close the string, you can use a special JavaScript notation to replace the variable with its value, ${MaxDate}.

Your definition of the insert statement would look like this:

   var cmd=` Insert into PersonTable   

    select SHA1(concat(Person_id,'|','Person')) ,12345678,SHA1(concat('Payroll','|','Pay','|', Load_Date)) ,current_timestamp() , Tenant
    from Schema.PERSONTABLE where Date_Added >= ${MaxDate}

    where  TYPE='ABC' ;`;

If that doesn't work, try cutting the SP short with return MaxDate; to see what got assigned to that variable. Also it's very helpful to check the query history view to see what SQL actually ran inside a stored procedure.

Also, I think this is the same SP that was having an issue with a null return. You'll need to return a string value using something like return 'Success'; or something to avoid getting an error for the null return. That's because of the returns varchar not null in the definition.

user_127563810
Related