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.
REPLACE PROCEDURE SAMPLE_SP ( OUT n INTEGER )
BEGIN
DECLARE x INTEGER DEFAULT 99;
BEGIN
DECLARE x INTEGER DEFAULT 100;
SET n = x;
END;
END;
CALL SAMPLE_SP(x);
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.
REPLACE PROCEDURE SAMPLE_SP ( OUT n INTEGER )
l1: BEGIN
DECLARE x INTEGER DEFAULT 99;
BEGIN
DECLARE 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.
Hi Roland, Is there any alternative in Teradata to use variables apart from stored procedures, can we declare variables in normal sql scripts.
Your Postings are really worth reading and knowledge gaining. Thanks for sharing the knowledge