Example: Input and Output Arguments in BTEQ and CLIv2 - 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
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

You can use an SQL SHOW PROCEDURE statement to view and verify the parameter types:

     CREATE PROCEDURE spSample2(OUT p1 INTEGER, INOUT p2 INTEGER, 
     IN p3 INTEGER)
      BEGIN
         SET p1 = p3;
         SET p2 = p2 * p3;
      END;

The call arguments can be specified in any of the following formats:

Format 1:

     CALL spSample2(p1, 20, 3);

where the parameter name p1 is supplied as an argument because it is a placeholder for an OUT parameter. The values 20 and 3 are passed as arguments to the INOUT and IN parameters, p2 and p3 respectively.

The CALL statement returns the following response:

                 p1           p2
        -----------  -----------
                  3           60

Format 2:

     CALL spSample2(p1, 20 * 2, 30 + 40);

where the expressions (20 * 2) and (30 + 40) are passed as arguments to the INOUT and IN parameters, p2 and p3.

The CALL statement returns the following response:

              p1           p2
     -----------  -----------
              70         2800

Format 3:

     CALL spSample2(CAST(CAST(p1 AS CHARACTER(10)) AS TITLE      'OutputValue'), CAST(20 AS SMALLINT), 30 + 40);

where the expressions CAST(20 AS SMALLINT) and (30 + 40) are passed as arguments to the INOUT and IN parameters, p2 and p3.

The CALL statement returns the following response:

     OutputValue      20
     -----------  ----------
     70               1400

Format 4:

This format is used with BTEQ and assumes that data is being imported from a data file.

     USING (a INTEGER) CALL spSample1(p1, :a, 3);

where 3 is the value passed as an argument to the IN parameter p3. The value read from the input data file is assigned to the variable a and is passed as an argument to the INOUT parameter p2 via a USING clause.

BTEQ receives the following response from the Vantage platform:

              p1          p2
     -----------  -----------
               3          30

Format 5 (Using NAMED and TITLE phrases in the CALL request):

     CALL spSample1(CAST (p1 AS NAMED AA TITLE 'OUT VALUE'),
     CAST (((20 * 2) + 3) AS TITLE 'INOUT VALUE'), 1);

The response looks like this:

       OUT VALUE  INOUT VALUE
     -----------  ------------
               1            43