In a macro all DML statements are treated as a single request (and therefore) part of a single transaction. If one fails they all fail. In a SP you can code around this.
A macro can be created in any transaction mode and executed in any transaction mode. The SP must be called in the same transaction mode that it was created in.
IMHO a macro is a lot easier to code than a SP.
If you CREATE (or replace) a SQL Stored Procedure in Teradata mode, that procedure can only be called from sessions using Teradata mode. If you Create the SP in ANSI mode, then it can only be called from sessions using ANSI mode. Error handling and transaction semantics are different depending on the mode - see the SQL Reference - Stored Procedures and Embedded SQL manual.
With SQL SECURITY OWNER, SP defined in A will check DB_A's permissions regardless of how it is invoked. Note that the special CREATE OWNER PROCEDURE right is required to create a procedure that uses SQL SECURITY OWNER. This is also explained in the same manual.
One more question. I have an existing SP A created with SQL SECURITY CREATOR and this SP is called in another SP B in ETL. I am thinking of replacing SP A with SQL SECURITY OWNER so that it is freed from a particular User and only depends on the DB. Will this change have an effect on the call of SP B in ETL?
PS It would have been nice if there was a SQL SECURITY option for the Role of the creator and not the creator itself. And during compilation and execution the rights of the Role is checked and not the user.
Changing the SQL SECURITY clause for the called procedure will have no effect on the calling procedure.
Your comment brings up an interesting technical point. A role is effective in connection with a session for a user, and may or may not be "active" for any given session. This may not be obvious, since many sites will set ALL granted roles being active as the default for most users.
As it relates to Stored Procedures, when Creator rights are being checked at CREATE/REPLACE time, then rights from a role active in that session will be considered. But if Creator rights are being checked at execution time, only rights granted directly to the user are in effect.
DBC.UsersV view (and DBC.DBase table) have a RoleName column that names the default role (or the string 'All') if you are using database authorization.
If you are using directory-based authorization (external roles mapped at logon), you'd need to check in the directory.
It certainly sounds as if CREATOR security could be the problem in this situation. You might consider simply using INVOKER security; then execution of the SP would depend on the ETL user's permissions.
And if the ALL role is not set by default most end users will enable it manually as soon as they notice it's possible :-)
You can query dbc.SessionInfoV.CurrentRole to see the roles of the currently logged on sessions.