Snowflake IF ELSE Stored Procedure logic

Question

I have a tsql code that I'm tryinng to migrate into stored proc on Snowflake. I have a version 1 of the proc built and in production. BUT now have to do a lookup change based on IF-ELSE Logic. I can easily do it as suggested on T-sql but how do I accomplish the same on Snowflake. Below is the code on TSQL side that I wish to convert onto Snowflake.

if object_id ('tempdb..#CTE_Y_Date') is not null drop table #CTE_Y_Date;
if object_id ('tempdb..#CTE_PY_Date') is not null drop table #CTE_PY_Date;

declare @week53_check int 
,@Cal_Date int = 20210101
select @week53_check = Calendar_Week FROM calendar where  cast([Calendar_Date] as varchar) = @Cal_Date
--print @week53_check


SELECT [Calendar_Date]
      ,[Fiscal_Year]
      ,[Fiscal_Period]
      ,[Calendar_Week]
      ,[Fiscal_Period_Week]
      ,[Fiscal_Period_Day]
      ,[Begnning_Date_PV]
      ,Fiscal_Quarter = case when  Fiscal_Period between 1 and 3 then 1
                            when  Fiscal_Period between 4 and 6 then 2
                            when  Fiscal_Period between 7 and 9 then 3
                            when  Fiscal_Period between 10 and 12 then 4 else 0 end
into #CTE_Y_Date
  FROM calendar
  where  cast([Calendar_Date] as varchar) = @Cal_Date

  -- select * from #CTE_Y_Date

if @week53_check = 53 
(
SELECT a.[Calendar_Date]
      ,a.[Fiscal_Year]
      ,a.[Fiscal_Period]
      ,a.[Calendar_Week]
      ,a.[Fiscal_Period_Week]
      ,a.[Fiscal_Period_Day]
      --,[Fiscal_Date]
     -- ,[Begnning_Date]
      ,a.[Begnning_Date_PV]
       ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                            when  a.Fiscal_Period between 4 and 6 then 2
                            when  a.Fiscal_Period between 7 and 9 then 3
                            when  a.Fiscal_Period between 10 and 12 then 4 else 0 end
into #CTE_PY_Date
  FROM calendar a
  left join #CTE_Y_Date b on   a.[Fiscal_Year] = b.[Fiscal_Year] and a.[Fiscal_Period_Day] = b.[Fiscal_Period_Day]
  where  a.[Fiscal_Year] = b.[Fiscal_Year] and a.Calendar_Week = 1
)

else  insert into #CTE_PY_Date
SELECT a.[Calendar_Date]
      ,a.[Fiscal_Year]
      ,a.[Fiscal_Period]
      ,a.[Calendar_Week]
      ,a.[Fiscal_Period_Week]
      ,a.[Fiscal_Period_Day]
      --,[Fiscal_Date]
     -- ,[Begnning_Date]
      ,a.[Begnning_Date_PV]
       ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                            when  a.Fiscal_Period between 4 and 6 then 2
                            when  a.Fiscal_Period between 7 and 9 then 3
                            when  a.Fiscal_Period between 10 and 12 then 4 else 0 end

  FROM calendar a
  left join #CTE_Y_Date b on  a.Fiscal_Period = b.Fiscal_Period and a.Fiscal_Period_Week = b.Fiscal_Period_Week and a.[Fiscal_Period_Day] = b.[Fiscal_Period_Day]
  where  a.[Fiscal_Year] = b.[Fiscal_Year] - 1

select * from #CTE_PY_Date

Snowflake code version 1 is here. I need to accomplish the IF block from above Tsql here.

CREATE or replace procedure  "SP_53WeeksTest"()
returns FLOAT
language javascript
as
$$
//drop temp tables

var  dtt_CTE_Y_Date = 'drop table if exists CTE_Y_Date';
var dtt_CTE_Y_Date_stmt =  snowflake.createStatement({sqlText: dtt_CTE_Y_Date});  
var dtt_CTE_Y_Date_res = dtt_CTE_Y_Date_stmt.execute();

var  dtt_CTE_PY_Date = 'drop table if exists CTE_PY_Date';
var dtt_CTE_PY_Date_stmt =  snowflake.createStatement({sqlText: dtt_CTE_PY_Date});  
var dtt_CTE_PY_Date_res = dtt_CTE_PY_Date_stmt.execute();

var cttCTE_Y_Date = `create temporary table CTE_Y_Date (CALENDAR_DATE NUMBER(38,0)
                                   ,FISCAL_YEAR NUMBER(38,0)
                                   ,FISCAL_PERIOD NUMBER(38,0)
                                   ,CALENDAR_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_DAY NUMBER(38,0)
                                   ,BEGNNING_DATE_PV DATE
                                  ,Fiscal_Quarter NUMBER(38,0)
                                  )`;
var cttCTE_Y_Date_stmt = snowflake.createStatement({sqlText: cttCTE_Y_Date});
var cttCTE_Y_Date_res= cttCTE_Y_Date_stmt.execute();

var CTE_Y_Date_insert = `insert into CTE_Y_Date
select CALENDAR_DATE,FISCAL_YEAR,FISCAL_PERIOD,CALENDAR_WEEK,FISCAL_PERIOD_WEEK,FISCAL_PERIOD_DAY,BEGNNING_DATE_PV
 ,case when  Fiscal_Period between 1 and 3 then 1
when  Fiscal_Period between 4 and 6 then 2
when  Fiscal_Period between 7 and 9 then 3
when  Fiscal_Period between 10 and 12 then 4 else 0 end as Fiscal_Quarter
from FISCAL_CALENDAR
where BEGNNING_DATE_PV = (select max(BUSINESS_DATE) from RAW_TABLE_DB.POS_TABLES.PS_TRANS_HEADER)`;
var CTE_Y_Date_insert_stmt = snowflake.createStatement({sqlText: CTE_Y_Date_insert});
var CTE_Y_Date_res= CTE_Y_Date_insert_stmt.execute();

var cttCTE_PY_Date = `create temporary table CTE_PY_Date (CALENDAR_DATE NUMBER(38,0)
                                   ,FISCAL_YEAR NUMBER(38,0)
                                   ,FISCAL_PERIOD NUMBER(38,0)
                                   ,CALENDAR_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_DAY NUMBER(38,0)
                                   ,BEGNNING_DATE_PV DATE
                                  ,Fiscal_Quarter NUMBER(38,0)
                                  )`;
var cttCTE_PY_Date_stmt = snowflake.createStatement({sqlText: cttCTE_PY_Date});
var cttCTE_PY_Date_res= cttCTE_PY_Date_stmt.execute();

var CTE_PY_Date_insert = `insert into CTE_PY_Date
select A.CALENDAR_DATE,A.FISCAL_YEAR,A.FISCAL_PERIOD,A.CALENDAR_WEEK,A.FISCAL_PERIOD_WEEK,A.FISCAL_PERIOD_DAY,A.BEGNNING_DATE_PV
 ,case when  A.Fiscal_Period between 1 and 3 then 1
when  A.Fiscal_Period between 4 and 6 then 2
when  A.Fiscal_Period between 7 and 9 then 3
when  A.Fiscal_Period between 10 and 12 then 4 else 0 end as Fiscal_Quarter
from FISCAL_CALENDAR AS A
LEFT JOIN CTE_Y_Date AS B ON A.FISCAL_PERIOD=B.FISCAL_PERIOD AND A.FISCAL_PERIOD_WEEK=B.FISCAL_PERIOD_WEEK AND A.FISCAL_PERIOD_DAY=B.FISCAL_PERIOD_DAY
WHERE A.FISCAL_YEAR = (B.FISCAL_YEAR - 1)`;
var CTE_PY_Date_insert_stmt = snowflake.createStatement({sqlText: CTE_PY_Date_insert});
var CTE_PY_Date_res= CTE_PY_Date_insert_stmt.execute();

return 0;
$$

Answer 1

I think that using some helpers helps moving code to snowflake javascript even easier. this is a common snippet I use:

// -------------- EXEC SNIPPET BEGIN --------------
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE, SQLSTATE, ERROR_HANDLERS, ACTIVITY_COUNT, INTO;
var fixBind = (arg) => arg instanceof Date && arg.toISOString() || arg || null;
var fetch = (count,rows,stmt) =>
    (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var INTO = () => (ROW_COUNT && fetch(ROW_COUNT,_ROWS,_RS)) || [];
var EXEC = function (stmt,binds,noCatch) {
    try {
        binds = (binds && binds.map(fixBind)) || binds;
        _RS = snowflake.createStatement({sqlText : stmt,binds : binds});
        _ROWS = _RS.execute();
        ROW_COUNT = _RS.getRowCount();
        ACTIVITY_COUNT = _RS.getNumRowsAffected();
        return { INTO: () => INTO() };
    }
    catch(error) {
        MESSAGE_TEXT = error.message;
        SQLCODE = error.code;
        SQLSTATE = error.state;
        if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error);
        var newError = Error(`SQLCODE: ${SQLCODE} MESSAGE: ${MESSAGE_TEXT}`)
        throw newError;
    }
};
// -------------- EXEC SNIPPET END --------------

I use it so much that I created a simple tool: https://github.com/orellabac/SnowJS-Helpers/ that allows me to just expand that code for deployment:

For example taking your code from above:

    CREATE or replace procedure  SP_53WeeksTest()
    returns FLOAT
    language javascript
    as
    $$
    "@USING_EXEC";
    EXEC(`DROP TABLE IF EXISTS tempdb."#CTE_Y_Date";`)
    EXEC(`DROP TABLE IF EXISTS tempdb."#CTE_PY_Date";`)
    
    var week53_check = 0;
    var Cal_Date = 20210101;
    
    EXEC(`select Calendar_Week FROM calendar where  cast(Calendar_Date as varchar) = ?`,[Cal_Date])
    [week53_check] = INTO();
    //print @week53_check
    
    EXEC(`
         INSERT INTO "CTE_Y_Date"
         SELECT Calendar_Date
          ,Fiscal_Year
          ,Fiscal_Period
          ,Calendar_Week
          ,Fiscal_Period_Week
          ,Fiscal_Period_Day
          ,Begnning_Date_PV
          ,Fiscal_Quarter = case when  Fiscal_Period between 1 and 3 then 1
                                when  Fiscal_Period between 4 and 6 then 2
                                when  Fiscal_Period between 7 and 9 then 3
                                when  Fiscal_Period between 10 and 12 then 4 else 0 end
        FROM calendar
        where  cast(Calendar_Date as varchar) = ?]`,[Cal_Date]);
    
      // select * from #CTE_Y_Date
    
    if (week53_check == 53 )
    {
    EXEC(`
    insert into "#CTE_PY_DATE"
    SELECT a.Calendar_Date
          ,a.Fiscal_Year
          ,a.Fiscal_Period
          ,a.Calendar_Week
          ,a.Fiscal_Period_Week
          ,a.Fiscal_Period_Day
          --,Fiscal_Date
         -- ,Begnning_Date
          ,a.Begnning_Date_PV
           ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                                when  a.Fiscal_Period between 4 and 6 then 2
                                when  a.Fiscal_Period between 7 and 9 then 3
                                when  a.Fiscal_Period between 10 and 12 then 4 else 0 end
      FROM calendar a
      left join "#CTE_Y_Date" b on   a.Fiscal_Year = b.Fiscal_Year and a.Fiscal_Period_Day = b.Fiscal_Period_Day
      where  a.Fiscal_Year = b.Fiscal_Year and a.Calendar_Week = 1
    )`,[]);
    }
    else {
    EXEC(`insert into "#CTE_PY_Date"
    SELECT a.Calendar_Date
          ,a.Fiscal_Year
          ,a.Fiscal_Period
          ,a.Calendar_Week
          ,a.Fiscal_Period_Week
          ,a.Fiscal_Period_Day
          --,Fiscal_Date
         -- ,Begnning_Date
          ,a.Begnning_Date_PV
           ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                                when  a.Fiscal_Period between 4 and 6 then 2
                                when  a.Fiscal_Period between 7 and 9 then 3
                                when  a.Fiscal_Period between 10 and 12 then 4 else 0 end
    
      FROM calendar a
      left join "#CTE_Y_Date" b on  a.Fiscal_Period = b.Fiscal_Period and a.Fiscal_Period_Week = b.Fiscal_Period_Week and a.Fiscal_Period_Day = b.Fiscal_Period_Day
      where  a.Fiscal_Year = b.Fiscal_Year - 1`);
      // If you want to return this last statement
      // This is want i do, I use a temporary table which I drop before use to make sure it was empty and the from the caller
      // SP just select from RESULTS_SP_53WeeksTest
      EXEC("DROP TABLE IF EXISTS RESULTS_SP_53WeeksTest",[]);
      EXEC(`CREATE TEMPORARY TABLE RESULTS_SP_53WeeksTest AS select * from "#CTE_PY_Date"`,[]);
}
      $$;

I just use my tool to expand the EXEC snippet before deployment but If you want just copy paste the EXEC snippet instead of the "@USING_EXEC" comment

Answer 2

T-SQL stored procedures are a top-to-bottom flow. Snowflake stored procedures (for now) are JavaScript, which should be modularized.

To look up a single value and store in a variable, you'll want to use a helper function like this:

function getSingleValueQuery(columnName, queryString) {
    var out;
    cmd1 = {sqlText: queryString};
    stmt = snowflake.createStatement(cmd1);
    var rs;

    rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(columnName);
    return out;
}

This makes it easier and more readable to do repetitive tasks like looking running an query and returning the results when you expect only a single-row return.

You can then call the function and store the result in a variable something like this depending on your data structure:

var calDate = 20210101;
var week53_check = getSingleValueQuery('CALENDAR_WEEK', 
          `select CALENDAR_WEEK from CALENDAR where CALENDAR_DATE = ${calDate}`);

Then it's just a matter of setting up an if-then block on the condition of week53_check like so:

if (week53_check == 53) {
  // Do this
} else 
  // Do something else
}

For best readability, I recommend modularizing your code using helper functions:

if (week53_check == 53) {
  doWeek53Insert();
} else 
  doNonWeek53Insert();
}

You can then have two functions like this:

function doWeek53Insert() {
    var out;
    cmd1 = {sqlText: `insert into CTE_Y_Date... (etc)... `};
    stmt = snowflake.createStatement(cmd1);
    var rs;
    rs = stmt.execute();
}