title: How to use variables in Teradata SQL Macros
body: <p>I'm wanting to use variables inside my macro SQL on Teradata.</p>
  
  <p>I thought I could do something like the following:</p>
  
  <pre><code>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  
     );  
  
  );  
  </code></pre>
  
  <p>However, that didn't work, so I thought of this instead:</p>
  
  <pre><code>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  
     );  
  
  );  
  </code></pre>
  
  <p>I can do what I'm looking for with a Stored Procedure, however I want to avoid for performance.</p>
  
  <p>Do you have any ideas about this?<br>
  Is there anything else I can try?</p>
  
  <p>Cheers<br>
  Tim</p>