Archives of the TeradataForum

Message Posted: Tue, 26 Nov 2002 @ 05:44:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>