16.20 - parameter_name - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

The name of a parameter that is replaced with a value during macro execution. UDT columns and TOP n operators are valid parameters.

For information about naming database objects, see Teradata Vantage™ SQL Fundamentals, B035-1141.

Parameters are restricted to data values. You cannot parameterize database object names.

When referenced in the macro body, you must prefix a parameter name with the COLON ( : ) character.

type_declaration
A data definition or default definition for a parameter.
If you do not assign a default value, you must specify a value for the parameter at EXECUTE time.
UDTs are valid data types.
You cannot specify a character server data set of KANJI1 for CHARACTER, VARCHAR, LONGVARCHAR, GRAPHIC, VARGRAPHIC, or CLOB data. If you attempt to do so, Teradata Database aborts the request and returns an error to the requestor.
For a list of data types see Teradata Vantage™ Data Types and Literals, B035-1143.
type_attribute
The data type attributes, such as NOT NULL, UPPERCASE, or TITLE.
The following column attributes are never valid with macro parameters:
  • CHECK constraints
  • COMPRESS phrase
For a list of data type attributes see Teradata Vantage™ Data Types and Literals, B035-1143.

Example: Macro Support for UDT Parameters

In this example, the p2 parameter of the macro is a VARCHAR, which is passed through to the macro. The conversion is performed within the macro itself using the NEW constructor expression. See “NEW” in Teradata Vantage™ SQL Operators and User-Defined Functions, B035-1210.

CREATE MACRO m2 (p1 integer, p2 VARCHAR(100)) 
AS (INSERT t1(:p1, NEW structured_type(:p2)););
USING (a INTEGER, b VARCHAR(100))
EXEC m2(:a, :b);

Example: Macro Support for UDT Parameters with Triggers

Suppose you have a macro named ins_mail_list that inserts the first name, last name, and home address of new customers into a table named mailing_addresses. The value of the customer home address is defined with a structured UDT named address.

The macro is defined as follows:

CREATE MACRO insert_mail_list (first VARCHAR(15), last VARCHAR(15), 
                               addr address)
AS (INSERT INTO mailing_addresses VALUES (:first, :last, :addr););

The following trigger adds new California customers to a mailing list whenever a new customer row is inserted into the customer table. The insert into the mailing_addresses table is done using the triggered SQL action statement, the macro insert_mail_list, which accepts the UDT parameter address:

CREATE TRIGGER ca_mailing_list 
AFTER INSERT ON customer
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.address.state() = 'CA')
EXEC insert_mail_list(newrow.name.last(), newrow.name.first(),
     newrow.address);

Example: Passing a TOP n Value Into a Macro as a Parameter

This example passes the integer value of n for the TOP n operator into a macro as a parameter named p.

CREATE MACRO m (p INTEGER)
AS (SELECT TOP :p x1 
    FROM t1;);

Example: Setting the Transaction Query Band Using a Parameter

The following macro sets the transaction query band using the parameter qbin:

CREATE MACRO qbmac (p1 INTEGER, p2 INTEGER, qbin VARCHAR(200)
AS (SET QUERY_BAND = :qbin FOR TRANSACTION
    SELECT GetQueryBand(););
 *** Macro has been created.
 *** Total elapsed time was 1 second.
EXEC qbmac (5,10,'music=classical;musician=david_tudor;');
 *** Set QUERY_BAND accepted.
*** Total elapsed time was 1 second.
 *** Query completed. One row found. One column returned.
GetQueryBand()
--------------------------------------------------------
=T> music=classical;musician=david_tudor;

Example: Invoking an SQL UDF From a Macro Definition

The following example invokes the SQL UDF common_value_expression within a SELECT request encapsulated within the definition of the macro m1.

CREATE MACRO m1 (a1 INTEGER, b1 INTEGER, c1 INTEGER)
AS (SELECT test.common_value_expression(:a1 = :b1) 
    FROM t1 
    WHERE t1.a1 = :c1; );