Example: Stored Procedure and Embedded SQL Input Arguments - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Consider the stored procedure spSample2 defined in Example: Input and Output Arguments in BTEQ and CLIv2. The arguments can be specified in any of the formats shown:

Format 1:

Only literals or constant expressions are specified as arguments for the parameters:
  • In a stored procedure:
         CALL spSample2(1, 2, 3 + 4);
       
  • In a C program using embedded SQL:
         EXEC SQL CALL spSample2(1, 2, 3 + 4);

Format 2:

The application variables contain the values that are passed as arguments:
  • In a stored procedure:
         SET AppVar1 = 10;
         SET AppVar2 = 30;
         SET AppVar3 = 40;
         CALL spSample2(:AppVar1, :AppVar1 + :AppVar2, CAST(:AppVar3 AS
         FORMAT 'Z,ZZ9'));
  • In a C program using embedded SQL:
         AppVar1 = 10;
         AppVar2 = 30;
         AppVar3 = 40;
         EXEC SQL CALL spSample2(:AppVar1, :AppVar1 + :AppVar2,
                       CAST(:AppVar3 AS FORMAT 'Z,ZZ9'));

Format 3:

The combination of the application variables (AppVar1, AppVar2, and AppVar3) and values/expressions are specified as arguments:
  • In a stored procedure:
         SET AppVar1 = 10;
         SET AppVar2 = 30;
         SET AppVar3 = 40;
         CALL spSample2(:AppVar1, 3 + :AppVar2, 3 + 4 + :AppVar3);
       
  • In a C program using embedded SQL:
         AppVar1 = 10;
         AppVar2 = 30;
         AppVar3 = 40;
         EXEC SQL CALL spSample2(:AppVar1, 3 + :AppVar2, 3 + 4
                       + :AppVar3);

No output parameters are returned from the stored procedure using this format, so the ACTIVITY_COUNT is returned as 0 in the success response.