15.00 - Rules for Using Parameters in Macros - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules for Using Parameters in Macros

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.

    If you need this functionality, you can use dynamic SQL within a stored procedure (see SQL Stored Procedures and Embedded SQL for information on how to use dynamic SQL within a stored procedure).

  • 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 SQL Data Manipulation Language for details).

  • 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.