Teradata Stored Procedures – Scope of variable declarations

1
2309
teradata stored procedure

 

teradata stored procedure

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

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

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

When executing above Teradata stored procedure, it will return the value 100 back 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).

Our Reader Score
[Total: 5    Average: 4.2/5]
Teradata Stored Procedures – Scope of variable declarations written by Roland Wenzlofsky on September 9, 2014 average rating 4.2/5 - 5 user ratings

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here