Example: Input and Output Arguments in BTEQ and CLIv2 - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
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