Variable Declarations in Teradata Stored Procedures
Learn how variable declarations work in a Teradata Stored Procedure. Declarations are always local to the surrounding compound statement. See examples.
In Teradata Stored Procedures, variable declarations are limited to the surrounding compound statement (BEGIN...END) and all subsequent compound statements.
An integer variable, x, is declared in the outer and inner compound statements.
The outer compound statement can only access its own declaration of variable x. In contrast, the inner compound statement has access to both the outer x and its own declaration of variable x.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
REPLACEPROCEDURE SAMPLE_SP ( OUT n INTEGER )BEGINDECLARE x INTEGER DEFAULT 99;BEGINDECLARE x INTEGER DEFAULT 100;SET n = x;END;END;CALL SAMPLE_SP(x);
Get the next issue by email.
This Teradata stored procedure will return the value of 100 to the client.
You must utilize labels to retrieve the variable x declared in the outer compound statement.
REPLACEPROCEDURE SAMPLE_SP ( OUT n INTEGER )l1: BEGINDECLARE x INTEGER DEFAULT 99;BEGINDECLARE x INTEGER DEFAULT 100;SET n = l1.x;END;END l1;CALL SAMPLE_SP(x);
Prefacing the variable with the outer compound statement label grants access to variable x declared therein. Consequently, the client (BTEQ, SQL Assistant) receives a value of 99 in the second example.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.