Archives of the TeradataForum
Message Posted: Tue, 26 Nov 2002 @ 05:44:25 GMT
Although both a macro and a stored procedure can, in Teradata, be used for some of the same things-e.g., they both support parameterized SQL-they are really very different.
A macro has very restricted control logic that is limited to performing or not performing ABORT statements, which undo changes. A stored procedure, on the other hand, supports extensive procedural logic and more sophisticated error handling. It can also perform looping logic or pass along statement values during a procedure. However, macros can return a multirow answer set, while stored procedures can only send back a single set of values through the parameter list.
Under some conditions, such as the straightforward insert of multiple rows, a macro will perform better than a stored procedure because it bundles all statements into one multistatement reques-a single parsing-and-recovery unit. Stored procedures do not support multistatement requests, and each insert within a procedure is sent individually to the parser and dispatched as a separate request, eliminating the potential for parallel single-row inserts.
For simple activities, particularly those involving multiple, repetitious SQL or requiring multirow answer sets, use macros; where the required logic is beyond what a macro can accommodate, turn to stored procedures
- Narayan Murthy K S
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|