17.05 - Example: Stored Procedure and Embedded SQL Input Arguments - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.