A variable declared in a BEGIN…END compound statement is in scope in that block and its nested blocks, unless a nested declaration hides it. A separate later block does not inherit a declaration from an earlier sibling block.
An integer variable, x, is declared in the outer and inner compound statements.
An unqualified x inside the inner block resolves to the inner declaration. Qualifying x with the outer block label accesses the outer declaration; the outer block cannot reference the inner block’s local variable after that block ends.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThis 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.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
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