SQL Used to Create a Macro - Advanced SQL Engine - Teradata Database

Database Introduction

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
jiv1600056352873.ditamap
dita:ditavalPath
jiv1600056352873.ditaval
dita:id
B035-1091
lifecycle
previous
Product Category
Teradata Vantage™

You use the CREATE MACRO statement to create Teradata Database macros.

For example, suppose you want to define a macro for adding new employees to the Employee table and incrementing the EmpCount field in the Department table. The CREATE MACRO statement looks like this:

    CREATE MACRO NewEmp (name VARCHAR(12),
                         number INTEGER NOT NULL,
                         dept INTEGER DEFAULT 100
                        )
    AS (INSERT INTO Employee (Name,
                              EmpNo,
                              DeptNo
                             )
        VALUES (name,
               number,
                dept
               )
        ;
        UPDATE Department
        SET EmpCount=EmpCount+1
        WHERE DeptNo=dept
        ;
       )
    ;

This macro defines parameters that users must fill in each time they execute the macro. A leading colon (:) indicates a reference to a parameter within the macro.