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

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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.