bteqMacro

Last updated: June 9, 2026

Provides a structured way to execute Teradata macros through the BTEQ executor. Generates an EXEC statement with optional typed parameters, replacing the need to write <sql>EXEC MacroName(...);</sql> by hand.

The primary value is readability and parameter validation. Parameter names serve as documentation, and the type attribute controls how each value is formatted in the generated SQL.

Note: Macros are imperative operations with arbitrary side effects. Automatic rollback is not possible. Provide an explicit <rollback> block or use <rollback/> to acknowledge irreversibility.

Available attributes

Attribute	Type	Required	Description
`name`	String	Yes	Macro name. Must be a valid Teradata identifier: starts with a letter or underscore, contains only letters, digits, and underscores.
`schemaName`	String	No	Database or schema containing the macro. When provided, the macro is referenced as `schemaName.name`. Must be a valid Teradata identifier.

Param attributes

Each <pro-teradata:param> element defines a positional parameter passed to the macro. Parameters are included in the EXEC statement in the order they appear in the XML.

Attribute	Type	Required	Description
`name`	String	Yes	Parameter name. Used for documentation and readability only; not included in the generated SQL.
`value`	String	Yes	Parameter value.
`type`	String	No	Controls how `value` is formatted in the generated `EXEC` statement. `STRING` wraps the value in single quotes and escapes internal single quotes. `NUMERIC` inserts the value without quotes. `DATE`, `TIMESTAMP`, and `TIME` prefix the value with the type keyword. Accepted values: `STRING`, `NUMERIC`, `DATE`, `TIMESTAMP`, `TIME`. Default: `STRING`.

Example code

Be sure to replace your_table, your_author, and your_schema with your actual values.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:pro-teradata="http://www.liquibase.org/xml/ns/pro-teradata"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/pro-teradata
        http://www.liquibase.org/xml/ns/pro-teradata/liquibase-pro-teradata-latest.xsd">

    <!-- Setup: create table and macros -->
    <changeSet id="macro-setup-tables" author="your_author">
        <preConditions onFail="MARK_RAN">
            <not><tableExists tableName="your_table"/></not>
        </preConditions>
        <createTable tableName="your_table">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="label" type="varchar(50)"/>
            <column name="amount" type="decimal(10,2)"/>
            <column name="recorded_date" type="date"/>
        </createTable>
        <sql>
            INSERT INTO your_table VALUES (1, 'Alpha', 100.00, DATE '2026-01-01');
            INSERT INTO your_table VALUES (2, 'Beta', 250.50, DATE '2026-01-15');
            INSERT INTO your_table VALUES (3, 'Gamma', 75.25, DATE '2026-02-01');
        </sql>
    </changeSet>

    <!-- Setup: create macros via BTEQ -->
    <changeSet id="macro-setup-no-params" author="your_author" runWith="bteq">
        <sql>
            CREATE MACRO CountAll AS (
                SELECT COUNT(*) AS total_rows FROM your_table;
            );
        </sql>
    </changeSet>

    <changeSet id="macro-setup-with-params" author="your_author" runWith="bteq">
        <sql>
            CREATE MACRO FilterByAmount (min_amount DECIMAL(10,2)) AS (
                SELECT id, label, amount
                FROM your_table
                WHERE amount >= :min_amount
                ORDER BY amount;
            );
        </sql>
    </changeSet>

    <changeSet id="macro-setup-date-param" author="your_author" runWith="bteq">
        <sql>
            CREATE MACRO FilterByDate (cutoff_date DATE) AS (
                SELECT id, label, recorded_date
                FROM your_table
                WHERE recorded_date >= :cutoff_date
                ORDER BY recorded_date;
            );
        </sql>
    </changeSet>

    <!-- Execute macro with no parameters -->
    <changeSet id="macro-no-params" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="CountAll"/>
    </changeSet>

    <!-- Execute macro with numeric parameter -->
    <changeSet id="macro-numeric-param" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="FilterByAmount">
            <pro-teradata:param name="min_amount" value="200" type="NUMERIC"/>
        </pro-teradata:bteqMacro>
    </changeSet>

    <!-- Execute macro with DATE parameter -->
    <changeSet id="macro-date-param" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="FilterByDate">
            <pro-teradata:param name="cutoff_date" value="2026-02-01" type="DATE"/>
        </pro-teradata:bteqMacro>
    </changeSet>

    <!-- Execute schema-qualified macro -->
    <changeSet id="macro-schema-qualified" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="CountAll" schemaName="your_schema"/>
    </changeSet>

    <!-- Execute macro with explicit rollback -->
    <changeSet id="run-swap" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="SwapPartition" schemaName="your_schema">
            <pro-teradata:param name="source" value="staging"/>
            <pro-teradata:param name="target" value="prod"/>
        </pro-teradata:bteqMacro>
        <rollback>
            <pro-teradata:bteqMacro name="SwapPartition" schemaName="your_schema">
                <pro-teradata:param name="source" value="prod"/>
                <pro-teradata:param name="target" value="staging"/>
            </pro-teradata:bteqMacro>
        </rollback>
    </changeSet>

</databaseChangeLog>

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "macro-no-params",
        "author": "your_author",
        "runWith": "bteq",
        "changes": [
          {
            "bteqMacro": {
              "name": "CountAll"
            }
          }
        ]
      }
    },
    {
      "changeSet": {
        "id": "macro-numeric-param",
        "author": "your_author",
        "runWith": "bteq",
        "changes": [
          {
            "bteqMacro": {
              "name": "FilterByAmount",
              "params": [
                { "param": { "name": "min_amount", "value": "200", "type": "NUMERIC" } }
              ]
            }
          }
        ]
      }
    },
    {
      "changeSet": {
        "id": "macro-date-param",
        "author": "your_author",
        "runWith": "bteq",
        "changes": [
          {
            "bteqMacro": {
              "name": "FilterByDate",
              "params": [
                { "param": { "name": "cutoff_date", "value": "2026-02-01", "type": "DATE" } }
              ]
            }
          }
        ]
      }
    }
  ]
}

databaseChangeLog:
  # Execute macro with no parameters
  - changeSet:
      id: macro-no-params
      author: your_author
      runWith: bteq
      changes:
        - bteqMacro:
            name: CountAll

  # Execute macro with numeric parameter
  - changeSet:
      id: macro-numeric-param
      author: your_author
      runWith: bteq
      changes:
        - bteqMacro:
            name: FilterByAmount
            params:
              - param:
                  name: min_amount
                  value: "200"
                  type: NUMERIC

  # Execute macro with DATE parameter
  - changeSet:
      id: macro-date-param
      author: your_author
      runWith: bteq
      changes:
        - bteqMacro:
            name: FilterByDate
            params:
              - param:
                  name: cutoff_date
                  value: "2026-02-01"
                  type: DATE

-- No parameters
EXEC CountAll

-- Numeric parameter
EXEC FilterByAmount(200)

-- DATE parameter
EXEC FilterByDate(DATE '2026-02-01')

-- Schema-qualified
EXEC your_schema.CountAll

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:pro-teradata="http://www.liquibase.org/xml/ns/pro-teradata"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/pro-teradata
        http://www.liquibase.org/xml/ns/pro-teradata/liquibase-pro-teradata-latest.xsd">

    <!-- Setup: create table and macros -->
    <changeSet id="macro-setup-tables" author="your_author">
        <preConditions onFail="MARK_RAN">
            <not><tableExists tableName="your_table"/></not>
        </preConditions>
        <createTable tableName="your_table">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="label" type="varchar(50)"/>
            <column name="amount" type="decimal(10,2)"/>
            <column name="recorded_date" type="date"/>
        </createTable>
        <sql>
            INSERT INTO your_table VALUES (1, 'Alpha', 100.00, DATE '2026-01-01');
            INSERT INTO your_table VALUES (2, 'Beta', 250.50, DATE '2026-01-15');
            INSERT INTO your_table VALUES (3, 'Gamma', 75.25, DATE '2026-02-01');
        </sql>
    </changeSet>

    <!-- Setup: create macros via BTEQ -->
    <changeSet id="macro-setup-no-params" author="your_author" runWith="bteq">
        <sql>
            CREATE MACRO CountAll AS (
                SELECT COUNT(*) AS total_rows FROM your_table;
            );
        </sql>
    </changeSet>

    <changeSet id="macro-setup-with-params" author="your_author" runWith="bteq">
        <sql>
            CREATE MACRO FilterByAmount (min_amount DECIMAL(10,2)) AS (
                SELECT id, label, amount
                FROM your_table
                WHERE amount >= :min_amount
                ORDER BY amount;
            );
        </sql>
    </changeSet>

    <changeSet id="macro-setup-date-param" author="your_author" runWith="bteq">
        <sql>
            CREATE MACRO FilterByDate (cutoff_date DATE) AS (
                SELECT id, label, recorded_date
                FROM your_table
                WHERE recorded_date >= :cutoff_date
                ORDER BY recorded_date;
            );
        </sql>
    </changeSet>

    <!-- Execute macro with no parameters -->
    <changeSet id="macro-no-params" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="CountAll"/>
    </changeSet>

    <!-- Execute macro with numeric parameter -->
    <changeSet id="macro-numeric-param" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="FilterByAmount">
            <pro-teradata:param name="min_amount" value="200" type="NUMERIC"/>
        </pro-teradata:bteqMacro>
    </changeSet>

    <!-- Execute macro with DATE parameter -->
    <changeSet id="macro-date-param" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="FilterByDate">
            <pro-teradata:param name="cutoff_date" value="2026-02-01" type="DATE"/>
        </pro-teradata:bteqMacro>
    </changeSet>

    <!-- Execute schema-qualified macro -->
    <changeSet id="macro-schema-qualified" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="CountAll" schemaName="your_schema"/>
    </changeSet>

    <!-- Execute macro with explicit rollback -->
    <changeSet id="run-swap" author="your_author" runWith="bteq">
        <pro-teradata:bteqMacro name="SwapPartition" schemaName="your_schema">
            <pro-teradata:param name="source" value="staging"/>
            <pro-teradata:param name="target" value="prod"/>
        </pro-teradata:bteqMacro>
        <rollback>
            <pro-teradata:bteqMacro name="SwapPartition" schemaName="your_schema">
                <pro-teradata:param name="source" value="prod"/>
                <pro-teradata:param name="target" value="staging"/>
            </pro-teradata:bteqMacro>
        </rollback>
    </changeSet>

</databaseChangeLog>

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "macro-no-params",
        "author": "your_author",
        "runWith": "bteq",
        "changes": [
          {
            "bteqMacro": {
              "name": "CountAll"
            }
          }
        ]
      }
    },
    {
      "changeSet": {
        "id": "macro-numeric-param",
        "author": "your_author",
        "runWith": "bteq",
        "changes": [
          {
            "bteqMacro": {
              "name": "FilterByAmount",
              "params": [
                { "param": { "name": "min_amount", "value": "200", "type": "NUMERIC" } }
              ]
            }
          }
        ]
      }
    },
    {
      "changeSet": {
        "id": "macro-date-param",
        "author": "your_author",
        "runWith": "bteq",
        "changes": [
          {
            "bteqMacro": {
              "name": "FilterByDate",
              "params": [
                { "param": { "name": "cutoff_date", "value": "2026-02-01", "type": "DATE" } }
              ]
            }
          }
        ]
      }
    }
  ]
}

databaseChangeLog:
  # Execute macro with no parameters
  - changeSet:
      id: macro-no-params
      author: your_author
      runWith: bteq
      changes:
        - bteqMacro:
            name: CountAll

  # Execute macro with numeric parameter
  - changeSet:
      id: macro-numeric-param
      author: your_author
      runWith: bteq
      changes:
        - bteqMacro:
            name: FilterByAmount
            params:
              - param:
                  name: min_amount
                  value: "200"
                  type: NUMERIC

  # Execute macro with DATE parameter
  - changeSet:
      id: macro-date-param
      author: your_author
      runWith: bteq
      changes:
        - bteqMacro:
            name: FilterByDate
            params:
              - param:
                  name: cutoff_date
                  value: "2026-02-01"
                  type: DATE

-- No parameters
EXEC CountAll

-- Numeric parameter
EXEC FilterByAmount(200)

-- DATE parameter
EXEC FilterByDate(DATE '2026-02-01')

-- Schema-qualified
EXEC your_schema.CountAll

bteqMacro

Available attributes

Param attributes

Example code

XML

JSON

YAML

SQL