Snowflake procedure, how to add a column in a select statement if a variable is true?

Question

I'm fairly new to snowflake and I need some help with the syntax. I need to include a new column in a select statement if a certain variable is TRUE. If the variable is FALSE then the column shouldnt be added.

Right now I have a procedure that looks like this (I'll try to keep it as tiny as possible)

CREATE OR REPLACE PROCEDURE XXX (CUSTOMER_ACCOUNT_ID VARCHAR, GROUP_BY ARRAY) 
RETURNS TABLE (
  CUSTOMER_ACCOUNT_ID VARCHAR,
  PRODUCT_NAME VARCHAR
)
LANGUAGE SQL 
COMMENT = 'Test'
AS 
$$

DECLARE 
    groupByConnector BOOLEAN;

BEGIN
-- This variable always receive TRUE or FALSE
groupByConnector:=ARRAY_CONTAINS('connector'::VARIANT, GROUP_BY);

res:= (

SELECT 
    SUM(xxx),
    SUM(yyy),
    IF (groupByConnector) THEN 'SUM(zzz)' END IF; -- HOW TO DO THIS?

FROM RANDOM_TABLE

);

RETURN TABLE(res);

END
$$;

So, as you guys can see, the 'IF (groupByConnector) THEN 'SUM(zzz)' END IF;' syntax is obviously wrong. How should I do it? Tried reading the documentation but couldnt find a solution yet.

Thanks!

Answer 1

you can use 2 approaches, one is use dynamic SQL and built the SQL if groupByConnector if it is True then have the column in the SQL or else dont have it The other option is some what like this,

-- Untested

  CREATE OR REPLACE PROCEDURE XXX (CUSTOMER_ACCOUNT_ID VARCHAR, GROUP_BY ARRAY) 
    RETURNS TABLE (
      CUSTOMER_ACCOUNT_ID VARCHAR,
      PRODUCT_NAME VARCHAR
    )
    LANGUAGE SQL 
    COMMENT = 'Test'
    AS 
    $$
    
    DECLARE 
        groupByConnector BOOLEAN;
    
    BEGIN
    -- This variable always receive TRUE or FALSE
    groupByConnector:=ARRAY_CONTAINS('connector'::VARIANT, GROUP_BY);
    
    res:= (
    
if groupByConnector THEN
    SELECT 
        SUM(xxx),
        SUM(yyy),
       'SUM(zzz)'   
    FROM RANDOM_TABLE;
   ELSE
    SELECT 
        SUM(xxx),
        SUM(yyy) FROM RANDOM_TABLE;
    END if;
    RETURN TABLE(res);
    
    END;
    $$;