Social Icons


TeradataWiki-Teradata Macros
A MACRO is a Teradata extension to ANSI SQL that contains prewritten SQL statements.
The actual text of the macro is stored in a global repository called the Data Dictionary (DD).

A macro allows you to name a set of one or more statements. When you need to execute those statements, simply execute the named macro. Macros provide a convenient shortcut for executing groups of frequently-run SQL statements.

Below is complete list of commands to manipulate macros.

CREATE MACRO macroname AS ( . . . );          Define a macro and store it in the DD.
EXEC macroname;                                             Execute statements within a macro.
SHOW MACRO macroname;                             Display a macro.
REPLACE MACRO macroname AS (. . . );        Apply changes to a macro or create a new one.
DROP MACRO macroname;                              Remove a macro definition from the DD.
EXPLAIN EXEC macroname;                            Display EXPLAIN text for the macro's execution.

Example for Creating Macro.

Create a macro to generate a birthday list for department 201:

   CREATE MACRO  birthday_list AS
                (SELECT   last_name
                FROM     employee
                WHERE    department_number =201
                ORDER BY birthdate;);

To execute the birthday list macro:

   EXEC birthday_list;

last_name        first_name        birthdate
---------------   ---------------    ---------------
Morrissey       Jim                 43/04/29
Short           Michael             47/07/07

Notice that there is a semicolon before the closing parenthesis. This is a required element of macro syntax.

Use the DROP MACRO command to delete a macro.

   DROP MACRO birthday_list; 

This command removes the macro from the containing database and also removes its entry from the Data Dictionary.

You can modify a micro by using REPLACE Macro.

The above macro is modified as below with REPLACE Macro command.

REPLACE MACRO    birthday_list AS
   /*  Macro is being updated for sorting sequence */
                   (SELECT    last_name
                   FROM       employee
                   WHERE      department_number = 201
                   ORDER BY   birthdate, last_name;);

Simple Parameterized Macros:

Parameterized macros allow substitutable variables. Values for these variables are supplied at runtime.

  CREATE MACRO dept_list (dept INTEGER)AS(
  SELECT   last_name
  FROM    employee
  WHERE   department_number = :dept;);

In parentheses following the macro name is the parameter list. It names each parameter followed by its data type. When a parameter is used in the body of a macro, it is always preceded by a colon.

 Macros with Multiple Parameters

CREATE MACRO emp_check (dept INTEGER
                            ,sal_amt DEC(9,2))
  (SELECT employee_number from employee
  WHERE department_number = :dept
  AND salary_amount < :sal_amt;);

EXEC emp_check (301, 50000);

Using a Parameterized Macro to Insert Data

  ( dept INTEGER
  , budget DEC(10,2) DEFAULT 0
  , name CHAR(30)
  , mgr INTEGER)
 ( INSERT INTO department
  ( department_number
  , department_name
  , budget_amount
  , manager_employee_number)
      VALUES (  :dept
                  ,  :name
                  ,  :budget
                  ,  :mgr )
  SELECT department_number (TITLE ‘Number’)
  ,department_name (TITLE ‘Name’)
  ,budget_amount (TITLE ‘Budget’)
  (TITLE ‘Manager’)
  FROM department
  WHERE department_number = :dept;


EXECUTE the INSERT MACRO (With Positional Parameter Values):
  • The macro consists of an INSERT statement followed by a SELECT.
  • The SELECT simply reads the row just inserted.
  • Input data must be in the order specified in the Macro parameter list.
  • Input data must match the exact number of parameters specified in the list.
  • Use the keyword NULL to explicitly pass a null to the macro.
  • Use positional commas to implicitly pass a null, or a specified default value.

 EXEC new_dept    (505 ,610000.00 , 'Marketing Research', 1007);

Number          Name                       Budget         Manager

---------          -------------                ------------      ------------
505 Marketing Research   610000.00     1007

EXEC new_dept (102 , , 'Payroll', NULL);

Number          Name                  Budget         Manager
---------          -------------           ------------      ------------
 102               Payroll                 .00                ?

In the example above, the value after 102 has been omitted, hence the two commas in a row. In such cases the parameter value will be set to the default specified for in the CREATE MACRO statement. In this case it is 0.


  1. why should macros in teradata? what is the purpose of macros in teradata?