Rules for Using Parameters in Macros - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™
Parameters are values that are entered by the user into the EXEC request for use by the macro during execution. The following rules apply to their use in CREATE MACRO and REPLACE MACRO requests:
  • You cannot pass the names of database objects to a macro as parameters.

    This refers to tables and views and their columns, databases, users, and similar database objects.

    You can use dynamic SQL within a stored procedure. For information on how to use dynamic SQL within a stored procedure, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.

  • Use of parameters is optional in a macro and, when required, must be specified as part of the CREATE MACRO or REPLACE MACRO request.
  • In defining parameters for a macro, follow the macro name in your CREATE/REPLACE MACRO request with the names and attributes of the appropriate parameters. Note that data type definitions are required for each parameter. Other attributes can include format specifications or default values. Define new formats via a FORMAT phrase and defaults by a default control phrase, as necessary.
  • If you specify them where either an ordinal positional integer number or an expression is valid, such as an ordinary grouping set in a GROUP BY clause or the ordering specification for an ORDER BY clause, Vantage treats parameters referenced by requests that are contained within a macro as expressions, not as constant literals.

    As a result, Vantage does not apply such parameter expressions as constant literals, if it applies them at all. Instead, you should specify grouping and ordering specifications explicitly within the body of the macro. See the information about the GROUP BY and ORDER BY clauses in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

  • If you supply a parameter value in the EXEC request that does not conform to the specified format, data type, or default value, the request aborts and returns a message to the requestor.
  • The maximum number of parameters you can specify per macro is 2,048.
  • If you specify a query band by means of a macro parameter, it can only be a transaction query band.

    If you submit a CREATE MACRO or RENAME MACRO request that uses a parameter to specify a session query band, the request aborts and the system returns a message to the requestor.