General Usage Guidelines with CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form) - Teradata Vantage

Default Session Collation for Procedures

The default server character set for a procedure is set by Teradata Support. If you want to change this default, contact Teradata Support.

Therefore, result sets returned to a client application or calling procedure use the collation established for the called procedure, even though they return results using the character set of the client application or procedure.

CREATE PROCEDURE and REPLACE PROCEDURE Invocation Restrictions

You cannot specify either CREATE PROCEDURE or REPLACE PROCEDURE requests as part of a procedure definition.

Memory Considerations 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 when the procedure was called, the CALL request fails and returns an overflow error to the requestor.

The following example shows this. Suppose you have created a procedure named myintz with a single INOUT parameter.

CALL myintz(32767);

The smallest data type the system can use to store 32,767 is SMALLINT, so it allocates the two bytes required to store a SMALLINT number for the parameter. If this CALL request returns a value greater than or equal to 32,768, the system treats it as an overflow for a SMALLINT irrespective of the data type assigned to the parameter when the procedure was created, aborts the request, and returns an error because the largest positive value that a SMALLINT variable can contain is 32,767.

Recompiling an SQL Procedure

Use the ALTER PROCEDURE (SQL Form) statement to recompile an existing SQL procedure.

SQL Procedure Support for Transaction Query Bands

You can set the transaction query band (but not the session query band) using a parameter value, including a QUESTION MARK parameter, that is passed to an SQL procedure.

SQL Procedures and Proxy Connections

SQL Procedure Support for UDTs

For correct operation of a UDT within a procedure, the UDT must have its required ordering and transform functionality defined. Additionally, the tosql and fromsql transform routines must be duplicated by an equivalent set of predefined data type-to-UDT and UDT-to-predefined data type implicit cast definitions. You can do this by referencing the same routines in both the CREATE TRANSFORM and CREATE CAST statements.

For distinct UDTs, if you plan to use the system-generated functionality, no additional work is required because the transform and implicit casting functionality have already been defined. For structured UDTs, you must explicitly define the transform and cast functionality, as shown by the following examples.

/* Transform Functionality */
    CREATE TRANSFORM FOR address ClientIO (
      TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress,
      FROM SQL WITH SPECIFIC METHOD toString);

/* Implicit Cast To Back Up The ToSql Functionality */
    CREATE CAST ( Varchar(100) AS address )
      WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress
      AS ASSIGNMENT;

/* Implicit Cast To Back Up The FromSql Functionality */
    CREATE CAST (address AS Varchar(100))
      WITH SPECIFIC METHOD  ToString
      AS ASSIGNMENT;

You can declare an input parameter to have the VARIANT_TYPE UDT data type only within the body of the procedure definition, which means that the system passes the dynamic UDT data type to this parameter position during runtime using the NEW VARIANT_TYPE expression. You cannot declare an IN parameter data type for the procedure itself to have the VARIANT_TYPE data type.

You cannot declare the data type for any parameter to be TD_ANYTYPE.

Using the CURRENT_TIME and CURRENT_TIMESTAMP Functions in an SQL Procedure

When you create a procedure that contains the CURRENT_TIME or CURRENT_TIMESTAMP functions, Vantage incorporates the value for the function at the time the procedure is compiled.

If the settings of these parameters adjust the time zone, any procedure created with the intent of returning a current value for the CURRENT_TIME or CURRENT_TIMESTAMP functions that is adjusted for the local time, no matter where the procedure is invoked, does not return the intended result.

You can recompile the procedure periodically, but that is not a universally applicable solution to the problem.

See Time Zone Strings for a complete list of valid time zone strings.

Using the PREPARE Statement in an SQL Procedure

Utilities and APIs Supporting CREATE/REPLACE PROCEDURE

You can create a procedure using the COMPILE command in BTEQ and BTEQWIN. See Basic Teradata® Query Reference, B035-2414.

You can also create a procedure using the CREATE PROCEDURE statement from CLIv2, ODBC, and JDBC applications.

For details on the use of local variables and condition handlers in SQL procedures, see the description of DECLARE and DECLARE HANDLER topics in Condition Handling.