16.20 - Rules for Using Parameters in Macros - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
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, Teradata Database treats parameters referenced by requests that are contained within a macro as expressions, not as constant literals.

    As a result, Teradata Database 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 “GROUP BY Clause” and “ORDER BY Clause” 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.