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.
he has privileges for the underlying tables or views that the macro uses.
What is diffeerence between macro and procedure ?
ReplyDeletewhich gud interm of performance?
Hi,
ReplyDeleteFor 1st question please check
http://www.teradatainterview.com/2011/01/difference-between-stored-procedure-and.html
For 2nd Question. This is very interesting part and sometimes it is straight forward to decide you need a procedure or macro.
- as a thumb rule , I would say
1. If you have set of sql queries to be run and large number of output statements then go for macro
2. If you have some logic to be implemented and based on some conditions the queries should execute then go for procedures.
hi
ReplyDeletehi there..
ReplyDeletei used to create procedure for looping date and or other stuffs.. how could i implement it in macro..
thanks.