CREATE MACRO

Translation reference to convert Teradata CREATE MACRO to Snowflake Scripting

Description

The Teradata CREATE MACRO defines one or more statements that are commonly used or that perform a complex operation, thus avoiding writing the same sequence of statements multiple times. The macro is executed when it is called by the EXECUTE statement.

For more information about CREATE MACRO click here.

CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( 
   <sql_statements> 
);

[ EXECUTE | EXEC ] <macroname>;

Sample Source Patterns

Setup data

The following code is necessary to execute the sample patterns present in this section.

IN -> Teradata_01.sql
CREATE TABLE DEPOSIT
(
    ACCOUNTNO NUMBER,
    ACCOUNTNAME VARCHAR(100)
);

INSERT INTO DEPOSIT VALUES (1, 'Account 1');
INSERT INTO DEPOSIT VALUES (2, 'Account 2');
INSERT INTO DEPOSIT VALUES (3, 'Account 3');
INSERT INTO DEPOSIT VALUES (4, 'Account 4');

Basic Macro

Since there is no macro object in Snowflake, the conversion tool transforms Teradata macros into Snowflake Scripting stored procedures. Besides, to replicate the functionality of the returned result set, in Snowflake Scripting, the query that is supposed to return a data set from a macro is assigned to a RESULTSET variable which will then be returned.

Teradata

IN -> Teradata_02.sql
REPLACE MACRO DEPOSITID (ID INT)
AS
(
  SELECT * FROM DEPOSIT WHERE ACCOUNTNO=:ID;
);

EXECUTE DEPOSITID(2);

Snowflake Scripting

OUT -> Teradata_02.sql
CREATE OR REPLACE PROCEDURE DEPOSITID (ID FLOAT)
RETURNS TABLE ()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET res RESULTSET := (SELECT * FROM DEPOSIT WHERE ACCOUNTNO=:ID);
        RETURN TABLE(res);
    END;
$$;

CALL DEPOSITID(2);

Macro Calls Another Macro

SnowConvert supports the scenario where a macro calls another macro and, by transitivity, a result set is returned.

Teradata

IN -> Teradata_03.sql
REPLACE MACRO MacroCallOtherMacro (ID INT)
AS
(
    EXECUTE DEPOSITID(:ID);
);

EXECUTE MacroCallOtherMacro(2);

Snowflake Scripting

OUT -> Teradata_03.sql
CREATE OR REPLACE PROCEDURE MacroCallOtherMacro (ID FLOAT)
RETURNS TABLE ()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
    LET res RESULTSET := (CALL DEPOSITID(:ID));
    RETURN TABLE(res);
    END;
$$;

CALL MacroCallOtherMacro(2);

Macro with no result set

Not all macros are intended to return a result set. The mentioned scenario is also supported.

Teradata

IN -> Teradata_04.sql
REPLACE MACRO MacroWithoutSelect (ACCOUNTNO NUMBER, ACCOUNTNAME VARCHAR(100))
AS
(
  INSERT INTO DEPOSIT VALUES (:ACCOUNTNO, :ACCOUNTNAME);
);

EXECUTE MacroWithoutSelect(5, 'Account 5');