fbpx

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

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

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

    You might also like

    >