Teradata Stored Procedures – Scope Of Variable Declarations

Roland Wenzlofsky

September 9, 2014

minutes reading time


Variable declarations in a Teradata Stored Procedure are always local to the surrounding compound statement (BEGIN…END) and to all the compound statements located within the surrounding BEGIN…END.

See the example below. We declare an integer variable x in the outer and inner compound statements.

While the outer compound statement only has access to its declaration of the variable x, the inner compound statement has access to the outer x and its 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);

The above Teradata stored procedure will return the value 100 to the client (BTEQ, SQL Assistant).

In case you would need to return the variable x, which is declared in the outer compound statement, you have to work with labels:

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);

Using the label of the outer compound statement as a prefix to the variable allows you to access the variable x declared in the outer compound statement. Therefore, the second example returns value 99 to the client (BTEQ, SQL Assistant).

  • 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

    >