Using variables inside macros in SQL
- by Tim
Hello
I'm wanting to use variables inside my macro SQL on Teradata.
I thought I could do something like the following:
REPLACE MACRO DbName.MyMacro
(
MacroNm VARCHAR(50)
)
AS
(
/* Variable to store last time the macro was run */
DECLARE V_LAST_RUN_DATE TIMESTAMP;
/* Get last run date and store in V_LAST_RUN_DATE */
SELECT LastDate
INTO V_LAST_RUN_DATE
FROM DbName.RunLog
WHERE MacroNm = :MacroNm;
/* Update the last run date to now and save the old date in history */
EXECUTE MACRO DbName.RunLogUpdater(
:MacroNm
,V_LAST_RUN_DATE
,CURRENT_TIMESTAMP
);
);
However, that didn't work, so I thought of this instead:
REPLACE MACRO DbName.MyMacro
(
MacroNm VARCHAR(50)
)
AS
(
/* Variable to store last time the macro was run */
CREATE VOLATILE TABLE MacroVars AS
(
SELECT LastDate AS V_LAST_RUN_DATE
FROM DbName.RunLog
WHERE MacroNm = :MacroNm;
)
WITH DATA ON COMMIT PRESERVE ROWS;
/* Update the last run date to now and save the old date in history */
EXECUTE MACRO DbName.RunLogUpdater(
:MacroNm
,SELECT V_LAST_RUN_DATE FROM MacroVars
,CURRENT_TIMESTAMP
);
);
I can do what I'm looking for with a Stored Procedure, however I want to avoid for performance.
Do you have any ideas about this?
Is there anything else I can try?
Cheers
Tim
