How to create and use Macro ?

A macro is a Teradata extension to ANSI SQL that contains pre written SQL  statements. Macros are used to run a repeatable set of tasks.The details of macro can be found in data dictionary (DD)  . Macros are database objects and thus they belong to a specified user or database. A macro can be executed by Queryman. ,  BTEQ, by another macro.


How to create a Macro
Create a macro to generate a DOB list for department 321:

CREATE MACRO DOB_Details AS
(SELECT  first_name ,last_name  ,DOB
FROM TERADATA.employees
WHERE dept_numbr =321
ORDER BY DOB asc;);

EXECUTE a Macro
To execute a macro, call it along with  the exec command.
EXEC DOB_Details;

last_name    first_name    DOB
Ram            Kumar         75/02/22
Laxman       Sinha           79/04/06

DROP a Macro
To drop a macro, use following command .
DROP MACRO DOB_Details;

REPLACE a Macro
If we need to modify an existing macro , instead of  dropping and re-creating it
We can use replace macro command as follows

REPLACE MACRO DOB_Details AS      
(SELECT first_name,last_name ,DOB
FROM TERADATA.employees
WHERE dept_numbr = 321
ORDER BY DOB, first_name;);

Parameterized Macros

Parametrized macros allow usage of variables .  we can pass values to these variables. Advantage of using parametrized macros is  , Values can be passed to these variables at run-time.

Example
CREATE MACRO dept_list (dept INTEGER) AS
(
SELECT last_name
FROM TERADATA.employees
WHERE dept_numbr = :dept; );

To Execute the macro
EXEC dept_list (321);

Macros may have more than one parameter. Each name and its associated type are separated by a comma from the next name and its associated type. The order is important. The first value in the EXEC of the macro will be associated with the first value in the parameter list. The second value in the EXEC is associated with the second value in the parameter list, and so on.

Example
CREATE MACRO emp_verify (dept_numbr INTEGER ,salary DEC(18,0))
AS (
SELECT emp_numbr
from TERADATA.employees
WHERE dept_numbr = :dept
AND salary< :sal;) ;

To Execute this  macro
EXEC emp_check (301, 50000);

Key points to note about Macros:

• Macros are a Teradata extension to SQL.
• Macros can only be executed with the EXEC privilege.
• Macros can provide column level security.

NOTE:  A user must have  EXEC privileges to execute the macros.  It doesn't matter if
he has privileges for the underlying tables or views that the macro uses.