Variable Declarations in Teradata Stored Procedures

Roland Wenzlofsky

April 23, 2023

minutes reading time


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

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >