EXECUTE (Macro Form) - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product

Teradata Database

Teradata Vantage NewSQL Engine

Release Number

16.20

Published

March 2019

Language

English (United States)

Last Update

2019-05-03

dita:mapPath

fbo1512081269404.ditamap

dita:ditavalPath

TD_DBS_16_20_Update1.ditaval

dita:id

B035-1146

lifecycle

Product Category

Teradata Vantage™

Purpose

Performs a macro.

For information about the embedded SQL EXEC statement, which is used to run macros from embedded SQL applications, see Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 .

Required Privileges

You must have EXECUTE privilege on the macro. The creator or any owner of the macro can grant the EXECUTE privilege to another. In addition, the immediate owner of the macro (the database in which the macro resides) must have the necessary privileges on objects named in the request set that are contained in the macro.

For more information, see Teradata Vantage™ SQL Data Definition Language Detailed Topics , B035-1184 and Teradata Vantage™ - Database Administration, B035-1093.

Syntax

Syntax Elements

macro_name: Name of the macro that is to be executed.
constant_expression: Constant or an expression involving constants that specifies a parameter value.; Values are listed in left-to-right order according to the order in which parameter names were specified in the CREATE MACRO request definition. You can specify nulls by typing successive COMMA characters, for example, (a, , b).; This is referred to as a positional parameter list.; If a value is not supplied for a parameter, a comma must be entered to account for the missing parameter.; Any default value defined for that parameter is inserted automatically.
parameter_name = constant_expression: Parameter name as defined in the CREATE MACRO request, and supplies the value for that parameter.; This is referred to as a named parameter list.; The value can be a constant, or an expression involving constants.; If a parameter is omitted, any default value defined for that parameter is inserted automatically.

ANSI Compliance

EXECUTE is a Teradata extension to the ANSI SQL:2011 standard.

Recommendation

A data definition request in a macro is not fully resolved until the macro is submitted for execution. Unqualified references to database objects are resolved at that time using the default database of the executing user.

Because of this, object references in data definition statements should always be fully qualified (as databasename.tablename) in the body of the macro.

Rules for Performing Macros

The following rules apply to performing macros.

The number of commas must match the macro definition if you do not use the parameter_name syntax.
Any value in the EXECUTE constant expression list form without a specified parameter name can be a constant or an expression involving constants. In this context, DATE, TIME, and USER are considered constants.
If an error message is returned when a macro is executed, it can be caused by an SQL request in the macro.
The number of parameters used in the calling sequence must be equal to the number of parameters defined.
When, for example, two parameters are defined and used, if they are both null, the following requests are all valid unless defaults are specified in the macro definition:
```
     EXECUTE macro_1 '(, 1);
     EXECUTE macro_1 (,);
     EXECUTE macro_1 (NULL, NULL);
```

Access Logging and Errors

Any syntactic or semantic error identified and reported by the SQL parser results in an error being returned to you without logging the request.

Example: Named Parameter List

This request uses a named parameter list to execute macro new_emp1. See “CREATE MACRO” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144. Named parameters can be listed in any order.

     EXECUTE new_emp1(number=10015, dept=500, name='Omura H', sex='M',
             position='Programmer');

The row for new employee Omura is inserted in the employee table.

Example: Positional Parameter List

This example uses a positional parameter list to execute macro new_emp2. See “CREATE MACRO” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144. Note that a value is not specified for the dept parameter, which has a macro-defined default value. A comma is entered in the dept position to maintain the integrity of the positional sequence.

     EXECUTE new_emp2 (10021, 'Smith T', , 'Manager', 'F', 
             'May 8, 1959', 16);

Example: Automatically Inserted Value

When the following request is processed, the default value for the dept parameter (900) is inserted automatically. The row for new employee Smith is added to the employee table, and then the department table is updated by incrementing the value for Department 900 in the emp_count column. The request uses a named parameter list to execute a macro named new_hire. Note that the value of the DOH (Date of Hire) column is an expression involving the DATE constant.

     EXECUTE new_hire (fl_name='Toby Smith', title='Programmer',
             doh=DATE -1);

Example: Invoking an SQL UDF as an Argument to Macro Execution

The following example invokes the SQL UDF value_expression as an argument to the macro m1.

     EXECUTE m1 (test.value_expression(1,2), 2, 3);