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

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.