Teradata - Macros

Macro is a set of SQL statements which are stored and executed by calpng the Macro name. The definition of Macros is stored in Data Dictionary. Users only need EXEC privilege to execute the Macro. Users don t need separate privileges on the database objects used inside the Macro. Macro statements are executed as a single transaction. If one of the SQL statements in Macro fails, then all the statements are rolled back. Macros can accept parameters. Macros can contain DDL statements, but that should be the last statement in Macro.

Create Macros

Macros are created using CREATE MACRO statement.

Syntax

Following is the generic syntax of CREATE MACRO command.

CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( 
   <sql statements> 
);

Example

Consider the following Employee table.

The following example creates a Macro called Get_Emp. It contains a select statement to retrieve records from employee table.

CREATE MACRO Get_Emp AS ( 
   SELECT 
   EmployeeNo, 
   FirstName, 
   LastName 
   FROM  
   employee 
   ORDER BY EmployeeNo; 
);

Executing Macros

Macros are executed using EXEC command.

Syntax

Following is the syntax of EXECUTE MACRO command.

EXEC <macroname>; 

Example

The following example executes the Macro names Get_Emp; When the following command is executed, it retrieves all records from employee table.

EXEC Get_Emp; 
*** Query completed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 second.  
EmployeeNo             FirstName                      LastName 
-----------  ------------------------------  --------------------------- 
   101                  Mike                          James 
   102                  Robert                        Wilpams 
   103                  Peter                         Paul 
   104                  Alex                          Stuart 
   105                  Robert                        James 

Parameterized Macros

Teradata Macros can accept parameters. Within a Macro, these parameters are referenced with ; (semicolon).

Following is an example of a Macro that accepts parameters.

CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( 
   SELECT 
   EmployeeNo, 
   NetPay 
   FROM  
   Salary 
   WHERE EmployeeNo = :EmployeeNo; 
);

Executing Parameterized Macros

Macros are executed using EXEC command. You need EXEC privilege to execute the Macros.

Syntax

Following is the syntax of EXECUTE MACRO statement.

EXEC <macroname>(value);

Example

The following example executes the Macro names Get_Emp; It accepts employee no as parameter and extracts records from employee table for that employee.

EXEC Get_Emp_Salary(101); 
*** Query completed. One row found. 2 columns returned. 
*** Total elapsed time was 1 second.
 
EmployeeNo      NetPay 
-----------  ------------ 
   101           36000