Examples - Teradata Database

Example: Input Arguments in BTEQ and CLIv2

Consider the following stored procedure spSample1 that has three IN parameters, p1, p2, and p3 of INTEGER data type. The argument list can be specified in either of the following formats:

     CALL spSample1 (1, 2, 3);

     CALL spSample1(1, CAST(((2 + 4) * 2) AS FORMAT 'ZZZ9'), 3);

The rule of specifying the arguments as a positional list applies to all arguments (IN, INOUT, or OUT).

Example: Input and Output Arguments in BTEQ and CLIv2

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:

     *** Procedure has been executed.
     *** Total elapsed time was 1 second.

                 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:

     *** Procedure has been executed..
      *** Total elapsed time was 1 second.
     
              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:

     *** Procedure has been executed.
      *** Total elapsed time was 1 second.

     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 Teradata platform:

     *** Procedure has been executed.
      *** Total elapsed time was 1 second.

              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:

     *** Procedure has been executed.
      *** Total elapsed time was 1 second.

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

Example: Stored Procedure and Embedded SQL Input Arguments

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);

Note that no output parameters are returned from the stored procedure using this format, so the ACTIVITY_COUNT is returned as 0 in the success response.

Example: Stored Procedures and Embedded SQL Input and Output Arguments

Consider the stored procedure spSample2. The arguments can be specified in the following format:

Format 1

In a stored procedure:

     SET AppVar2 = 30 + AppVar3;
     SET AppVar3 = 40;
     CALL spSample1(:AppVar1, :AppVar2, :AppVar3);

In a C program using embedded SQL:

     AppVar2 = 30 + AppVar3;
     AppVar3 = 40;
     EXEC SQL CALL spSample1(:AppVar1, :AppVar2, :AppVar3);

The values specified for AppVar2 and AppVar3 are passed as arguments to p2 and p3, respectively. When the stored procedure execution completes, the output parameter values are returned in AppVar1 and AppVar2. ACTIVITY_COUNT is set to 1.

Format 2

In a stored procedure:

     SET AppVar2 = 30 + AppVar3;
     SET AppVar3 = 40;
     CALL spSample1(:AppVar1, :AppVar2, :AppVar3 + 3);

In a C program using embedded SQL:

     AppVar2 = 30 + AppVar3;
     AppVar3 = 40;
     EXEC SQL CALL spSample1(:AppVar1, :AppVar2, :AppVar3 + 3);

The values for p2 and p3 are AppVar2 and (3 + AppVar3), respectively. When the stored procedure execution completes, the output parameter values are returned in AppVar1 and AppVar2. ACTIVITY_COUNT is set to 1.

Example: Preventing Memory Overflow Errors For INOUT Parameters

If the size of an output value returned to an INOUT parameter is larger than the memory the system had allocated for the input value for that parameter, the CALL request fails and returns an overflow error to the requestor. See Rules For Specifying Input And Output Parameters.

The following examples illustrate this. Suppose you have created an SQL stored procedure named myintz with an INOUT parameter.

Example 1

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     CALL myintz(32767);

The narrowest data type the system can fit 32,767 into is SMALLINT, so it allocates 2 bytes for the parameter and sets its type as SMALLINT irrespective of the data type assigned to the INOUT parameter when the procedure was created. If this CALL returns a value of 32,768 or more, the system treats it an a memory overflow for a SMALLINT variable and returns an error.

Example 2

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     CALL myintz(CAST ('32767' AS INTEGER));

In this case, the system knows the input value is an INTEGER value because of the explicit casting, so it allocates 4 bytes for the parameter. Then, when the CALL returns 32,768 to the INOUT parameter, the value is within the scope of an INTEGER, the request completes successfully, and no memory allocation error occurs.

     CALL myintz(cast ('32767' AS INTEGER));
      *** Procedure has been executed.
      *** Total elapsed time was 42 seconds.
         '32767'
     -----------
           32768

Similarly, if the client program code calling a procedure explicitly defines the data type for the parameter, and the specified data type is capable of containing a larger output value than the default type for the input value, the request completes successfully; otherwise, it does not.

The following examples illustrate this using a Java external stored procedure named myint2 called from a Java client application. Note that the basic principle applies for any stored procedure, not just external stored procedures. Assume the value returned to the INOUT parameter is 32,768 in both cases.

Example 3

Suppose you invoke the Java external stored procedure myint2 as follows:

     stmt.executeUpdate("call myint2(32767);");

The outcome of this call is a memory overflow error, which is identical to that seen in the first example.

Example 4

Suppose a Java client program invokes a Java external procedure as follows:

      StringBuffer prepCall = new StringBuffer("CALL myint2(?);");
      System.out.println(prepCall.toString());

      CallableStatement cStmt = con.prepareCall(prepCall.toString());

      cStmt.setInt(1, integerVar[0]);
      cStmt.registerOutParameter(1, Types.INTEGER);

      // Making a procedure call
      System.out.println("Before executeUpdate()...");

      cStmt.executeUpdate();
      System.out.println("Value after executeUpdate(): "
                         + cStmt.getInt(1));

      integerVar[0] = cStmt.getInt(1);

This CALL succeeds because the calling Java program explicitly defined the INOUT parameter as an INTEGER data type, so the system allocates an appropriate amount of memory for an INTEGER input value. As a result, no overflow error occurs when the returned value can be contained within the memory allocated to an INTEGER type.

Example: Input and Output Arguments in ODBC

The following example describes the usage of the stored procedures specifying input and output arguments in an ODBC application.

Consider the stored procedure spSample2 defined as follows:

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

The arguments can be specified in the following format:

     SQLBindParameter(..., 1, SQL_PARAM_INPUT_OUTPUT, ..., SQLINTEGER,      ..., ..., AppVar2, sizeof(AppVar2), ...);
     SQLBindParameter(..., 2, SQL_PARAM_INPUT, ..., SQLINTEGER, ...,      ..., AppVar3, sizeof(AppVar3), ...);

where the second argument in SQLBindParameter() is the question mark number ordered sequentially from left to right, starting at 1.

Executing the stored procedure:

     {
     constchar *request = "CALL spSample2(p1, ?, ?)";
     ...
     ...
     SQLExecDirect(hstmt, request);
     ...
     ...
     }

Retrieving the output parameter values:

     SQLBindCol(..., 1, ..., AppVar1, ..., ...);
     SQLBindCol(..., 2, ..., AppVar2, ..., ...);

where the second argument in the SQLBindCol() is the parameter number of result data, ordered sequentially left to right, starting at 1.

Example: Input and Output Arguments in JDBC

Consider the stored procedure spSample2. It can be executed using the following JDBC API calls:

     CallableStatement cstmt = con.prepareCall("CALL spSample2(p1, ?,      ?)");
     ResultSet rs = cstmt.executeQuery();

The following alternatives can be used for the second line (ResultSet rs = …):

     boolean bool = cstmt.execute();

or

     int count = cstmt.executeUpdate();

The QUESTION MARK character indicates an argument and acts as a placeholder for IN or INOUT parameters in the prepareCall() request. The question mark placeholder arguments must be bound with the application local variables and literals using the CallableStatement.setXXX() JDBC API calls.

Alternatively, an IN or INOUT argument can be a constant expression.

The INOUT and OUT arguments need to be registered using the following JDBC API call:

     CallableStatement.registerOutParameter()

After execution the parameter values can be retrieved from the response using the CallableStatement.getXXX() JDBC API calls.

Example: Calling a Stored Procedure That Returns Dynamic Result Sets From Embedded SQL

Consider the following stored procedure that returns a limit of four dynamic result sets to a calling embedded SQL application:

     CREATE PROCEDURE  TESTSP1(INOUT p1 INTEGER, OUT p2 INTEGER)
     DYNAMIC RESULT SETS 4
         BEGIN
          DECLARE tab1_cursor CURSOR WITH RETURN ONLY TO CALLER FOR
                              SELECT c1, c2, c3 FROM tab1;
                              OPEN tab1_cursor;
                              SET p1 = p1 + 1;
                              SET p2 = 82;
         END;

To access the dynamic result set rows returned to the application by this procedure, declare a cursor like the following:

     EXEC SQL BEGIN DECLARE SECTION;
          …
        long H1;
        long H2;

        long M1;
        long M2;
        long M3;
          …
     EXEC SQL END DECLARE SECTION;
          …
     EXEC SQL
            DECLARE TESTCUR CURSOR
                FOR 'CALL TESTSP1(:H1, :H2)';
     EXEC SQL
            OPEN TESTCUR;

If the procedure returns one or more rows, the system sets SQLCODE to 3212 and SQLSTATE to ‘T3212’. This example does not use SQLSTATE return codes. The SQLSTATE codes equivalent to the SQLCODE values in the example are provided for the sake of completeness.

If the stored procedure returns no rows, the system sets SQLCODE to 0 and SQLSTATE to ‘T0000’:

     if (SQLCODE == 3212)
           fetch_rows();
     else if (SQLCODE != 0)
           error_check();

Fetching rows from a cursor declared for a stored procedure is similar to fetching rows from a cursor defined for an SQL request:

     void fetch_rows()
              do  {
                          EXEC SQL
                               FETCH TESTCUR INTO :M1, :M2, :M3;
                          …
                  } while (SQLCODE == 0);

Example: Using an SQL UDF as an Argument for an External Stored Procedure

This example passes the SQL UDF value_expression as an argument to the external stored procedure spAccount.

     CALL spAccount(test.value_expression(3,4), outp1);

Example: Specifying a RETURNS or RETURNS STYLE Clause for an OUT Parameter Return Type

The first example shows how to use a RETURNS clause when invoking an external stored procedure. The RETURNS clause explicitly specifies a return type of INTEGER for the result_1 OUT parameter.

For this example to be valid, the OUT parameter result_1 must be defined with a data type of TD_ANYTYPE in xsp_1.

     CALL xsp_1(10.25, result_1 RETURNS INTEGER);

The second example shows how to specify a RETURNS STYLE clause when invoking an external stored procedure.

This example refers to table t1, which has the following definition.

     CREATE TABLE t1 (
       int_col     INTEGER,
       varchar_col VARCHAR(40) CHARACTER SET UNICODE);

The RETURNS STYLE clause implicitly specifies a return type of VARCHAR(40) CHARACTER SET UNICODE for the result_1 OUT parameter of xsp_2 because that is the data type for column varchar_col in table t1.

For this example to be valid, the OUT parameter result_1 must be defined with a data type of TD_ANYTYPE in xsp_2.

     CALL xsp_2(10, 'abc', result_1 RETURNS STYLE t1.varchar_col);

Example: Using RETURNS and RETURNS STYLE Clauses in the Same Procedure Call

This example specifies both RETURNS and RETURNS STYLE clauses in a CALL request using the following partial procedure definition.

     CREATE PROCEDURE XSP_1(
       IN A INT,
       OUT B TD_ANYTYPE,
       OUT C INT,
       OUT D TD_ANYTYPE, 
       OUT E INT,
       OUT F TD_ANYTYPE)
       …;

You might call xsp_1 using the following parameter specifications, mixing both RETURNS and RETURNS STYLE OUT parameter return types.

CALL myXSP1(10, RESULT_B RETURNS INTEGER, RESULT_C,
            RESULT_D RETURNS STYLE t1.int_col, 
            RESULT_E, RESULT_F RETURNS INTEGER);

Examples - Teradata Database

SQL Data Manipulation Language

Example: Input Arguments in BTEQ and CLIv2

Example: Input and Output Arguments in BTEQ and CLIv2

Example: Stored Procedure and Embedded SQL Input Arguments

Example: Stored Procedures and Embedded SQL Input and Output Arguments

Example: Preventing Memory Overflow Errors For INOUT Parameters

Example: Input and Output Arguments in ODBC

Example: Input and Output Arguments in JDBC

Example: Calling a Stored Procedure That Returns Dynamic Result Sets From Embedded SQL

Example: Using an SQL UDF as an Argument for an External Stored Procedure

Example: Specifying a RETURNS or RETURNS STYLE Clause for an OUT Parameter Return Type

Example: Using RETURNS and RETURNS STYLE Clauses in the Same Procedure Call

Related Topics