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
MSCEWI2067: Multiple result sets are returned in temporary tables.
Last updated
Was this helpful?