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.

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

REPLACE MACRO DEPOSITID (ID INT)
AS
(
  SELECT * FROM DEPOSIT WHERE ACCOUNTNO=:ID;
);

EXECUTE DEPOSITID(2);

Snowflake Scripting

CREATE OR REPLACE PROCEDURE DEPOSITID (ID FLOAT)
RETURNS TABLE()
LANGUAGE SQL
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

REPLACE MACRO MacroCallOtherMacro (ID INT)
AS
(
    EXECUTE DEPOSITID(:ID);
);

EXECUTE MacroCallOtherMacro(2);

Snowflake Scripting

CREATE OR REPLACE PROCEDURE MacroCallOtherMacro (ID FLOAT)
RETURNS TABLE()
LANGUAGE SQL
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

REPLACE MACRO MacroWithoutSelect (ACCOUNTNO NUMBER, ACCOUNTNAME VARCHAR(100))
AS
(
  INSERT INTO DEPOSIT VALUES (:ACCOUNTNO, :ACCOUNTNAME);
);

EXECUTE MacroWithoutSelect(5, 'Account 5');
SELECT * FROM DEPOSIT;

Snowflake Scripting

CREATE OR REPLACE PROCEDURE MacroWithoutSelect (ACCOUNTNO FLOAT, ACCOUNTNAME VARCHAR(100))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      INSERT INTO DEPOSIT VALUES (:ACCOUNTNO, :ACCOUNTNAME);
   END
$$;

CALL MacroWithoutSelect(5, 'Account 5');
SELECT * FROM DEPOSIT;

Macro returns multiple result sets

In Teradata, macros can return more than one result set from a single macro.

Snowflake Scripting procedures only allow one result set to be returned per procedure. To replicate Teradata behavior, when there are two or more result sets to return, they are stored in temporary tables. The Snowflake Scripting procedure will return an array containing the name of the temporary tables.

Teradata

REPLACE MACRO DEPOSITID (ID INT)
AS
(
  SELECT * FROM DEPOSIT WHERE ACCOUNTNO=4;
  SELECT * FROM DEPOSIT WHERE ACCOUNTNO=:ID;
);

EXECUTE DEPOSITID(2);

Snowflake Scripting

CREATE OR REPLACE PROCEDURE PUBLIC.DEPOSITID (ID FLOAT)
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
	return_arr ARRAY := array_construct();
	tbl_nm VARCHAR;
    BEGIN
	tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
	CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
	     SELECT * FROM PUBLIC.DEPOSIT WHERE ACCOUNTNO=4;
	return_arr := array_append(return_arr, :tbl_nm);
	
	tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
	CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
	     SELECT * FROM PUBLIC.DEPOSIT WHERE ACCOUNTNO=:ID;
	return_arr := array_append(return_arr, :tbl_nm);
	
	--** MSC-WARNING - MSCEWI2067 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
	RETURN return_arr;
    END;
$$;

CALL PUBLIC.DEPOSITID(2);

Visualize Result Rets

Executing the above procedure on Snowflake, an array with temporary table names in it will be returned:

[ "RESULTSET_93D50CBB_F22C_418A_A88C_4E1DE101B500", "RESULTSET_6BDE39D7_0554_406E_B52F_D9E863A3F15C"]

It is necessary to execute the following queries to display the result sets just like in Teradata.

SELECT * FROM table('RESULTSET_93D50CBB_F22C_418A_A88C_4E1DE101B500');
SELECT * FROM table('RESULTSET_6BDE39D7_0554_406E_B52F_D9E863A3F15C');

Known Issues

No issues were found.

Related EWIs

  1. MSCEWI2067: Multiple result sets are returned in temporary tables.

PreviousCASENextCREATE PROCEDURE

Last updated