CREATE MACRO and REPLACE MACRO Syntax Elements - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples
Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
database_name
The name of the containing database for macro_name if something other than the current database.
macro_name
The name of the new macro. If a fully qualified name is not specified, the default database or user is used.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
The name of a macro must be unique within its containing database.
using_modifier
One or more variable parameter names. A value is substituted for each parameter name when the request is processed. Also see “USING Request Modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. This is an optional phrase.
USING does not work in a macro when the macro is accessed using BTEQ. When running BTEQ, specify USING in the EXECUTE request.
Including USING in a macro might require special programming. See your Teradata Field support engineer or the Teradata Support Center for help.
locking modifier
A lock on a database, table, view or row hash.
The specified lock overrides the default usage lock placed in response to a request.
Also see “LOCKING Request Modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
SQL_statement
Specifies an SQL request. Every request in the macro body must be terminated by a SEMICOLON character.
Parameter names referenced in the macro body must be prefaced by the COLON ( : )) character. The macro body can include EXECUTE requests to invoke other macros.
SQL DCL and DDL statements for administration of row level security are not allowed in a macro.
parameter_name
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, Vantage 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.