DWHPro Insights

Variable Declarations in Teradata Stored Procedures

By Roland Wenzlofsky ·

Variable Declarations in Teradata Stored Procedures

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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

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
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.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “Variable Declarations in Teradata Stored Procedures”

  1. Hi Roland, Is there any alternative in Teradata to use variables apart from stored procedures, can we declare variables in normal sql scripts.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam