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

SQL Data Manipulation Language
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2025-04-02
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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.