15.10 - parameter_name - Teradata Database

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 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 SQL 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 SQL 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 SQL Functions, Operators, Expressions, and Predicates, B035-1145.

    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; );