Macros vs Stored Procedures

Database
Enthusiast

Macros vs Stored Procedures

We currently have multiple macros being used via one transaction call that potentially makes it dead lock prone. Would it be better to convert those macros into 1 SP?

On these lines, any advantages that SPs have over Macros or vice versa appreciated.

Thanks,
ShyGuy
3 REPLIES
Teradata Employee

Re: Macros vs Stored Procedures

We recommend going with Stored Procedure as all the complex SQL in macros can be disassembled and then easily integrated in stored procedures.

For difference between SP’s and Macros you can refer to Teradata manual “Stored Procedure and Embedded Sql” (Chapter 5 Sql Stored Procedure - Macros or Stored Procedures for Tactical Queries).

Thanks,
Anand (ADC)

Enthusiast

Re: Macros vs Stored Procedures

SPs are more favourable for situation needing procedural workaround not possible by simple SQL.It consume space and have a row by row operation. Macro on the other hand is a collection of many SQL statement ideal for commit or complete rollback situations. It doesnt take up any space but cannot offer procedural operations.
Hope it help.
Enthusiast

Re: Macros vs Stored Procedures

Thanks.