Variable Declarations in Teradata Stored Procedures

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.