ALTER PROCEDURE Examples | Teradata Vantage - Examples: ALTER PROCEDURE (SQL Form) - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

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-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

SQL Procedure for Use in Examples

The examples in this section refer to the following SQL procedure:

    CREATE PROCEDURE spAP2(IN  InParam  INTEGER, 
                           OUT OutParam INTEGER)
    BEGIN
       DECLARE Var1 INTEGER DEFAULT 10;
       SET OutParam = InParam + Var1 + 1;
    END;

The examples assume that the SQL procedure has been compiled as follows:

    BTEQ> .COMPILE FILE = testsp.spAP2

The SPL option is not specified, but the source text of the SQL procedure spAP2 is stored in the database because SPL is the default.

Example: Compiling an SQL Procedure with the AT TIME ZONE Option

This example illustrates the AT TIME ZONE option with an SQL-language procedure named spa_tz.

     ALTER PROCEDURE spa_tz
     COMPILE AT TIME ZONE LOCAL;

This option is only valid when it is specified following the COMPILE or COMPILE ONLY options.

For example, the following request is not valid because the AT TIME ZONE specification precedes the COMPILE specification in the request.

     ALTER PROCEDURE spa_tz 
     LANGUAGE SQL
     AT TIME ZONE COMPILE 'gmt';

The same request, when the options are specified in the correct sequence, is valid and alters the procedure as requested.

     ALTER PROCEDURE spa_tz 
     LANGUAGE SQL
     COMPILE AT TIME ZONE 'gmt';

Example: Impact of Session Mode on an ALTER PROCEDURE Statement

This example illustrates the behavior of ALTER PROCEDURE when the session mode is changed.

The procedure used to do this is as follows:

  1. Compile the procedure spAP3 in a Teradata session mode session.
         BTEQ> .COMPILE FILE spAP3.spl
  2. Change the session mode.
         .LOGOFF
         .SET SESSION TRANS ANSI
         .LOGON testsp, password
  3. Execute ALTER PROCEDURE.
         ALTER PROCEDURE ap3 
         COMPILE;

    The request fails with the following message.

         *** Failure 5510 Invalid session mode for procedure execution.
         Statement# 1, Info =0

    Because the execution of ALTER PROCEDURE failed, the existing version of the procedure spAP3 is retained.

Example: Recompiling an SQL Procedure with an ALTER PROCEDURE Statement, Failure Case

In this example, you execute an ALTER PROCEDURE request on a procedure originally compiled with NO SPL option. ALTER PROCEDURE fails because the source text of the procedure is not available for recompiling. The procedure was originally compiled with NO SPL option; as a result, the source text has not been stored in the database.

  1. Compile the procedure spAP2.
         .COMPILE FILE spAP2.spl WITH NOSPL
  2. Verify its attributes.
         HELP PROCEDURE spAP2 ATTR;

    Vantage returns the following report.

    Transaction Semantics TERADATA          Character Set ASCII
    Platform LINUX                          Collation ASCII
    Default Character DataType UNICODE      Version Number 11
    SPL Text N                              Print Mode N
    Default Database testsp
  3. Perform the ALTER PROCEDURE request.
         ALTER PROCEDURE spAP2 
         COMPILE;

    Vantage returns the following report.

         ALTER PROCEDURE spAP2 
         COMPILE;

    Result:

         *** Failure 5535 No SPL source text available for 
    stored procedure 'spAP2'.

    Because the execution of the ALTER PROCEDURE request fails, the existing version of the procedure spAP2 is retained.

Example: Recompiling an SQL Procedure with an ALTER PROCEDURE Statement, Successful Case

This example illustrates the use of ALTER PROCEDURE to recompile an SQL procedure with changed options. The example shows that the original character set of a procedure is not changed by ALTER PROCEDURE.

  1. Verify the current attributes of the procedure using HELP PROCEDURE.      
         HELP PROCEDURE spAP2 ATTR;

    Vantage returns the following report.

    Transaction Semantics TERADATA          Character Set EBCDIC
    Platform LINUX                          Collation ASCII
    Default Character DataType UNICODE      Version Number 17.10
    SPL Text Y                              Warning Option Y
    Default Database testsp

    The Version Number 17.10 indicates that the procedure was created in Analytics Database 17.10.

  2. Using BTEQ, change the session character set from EBCDIC to ASCII.
         .SET SESSION charset 'ASCII'
  3. Execute an ALTER PROCEDURE request that specifies the NO SPL and NO WARNING options.     
         ALTER PROCEDURE spAP2 
         COMPILE WITH NO SPL, NO WARNING;
  4. Check the procedure attributes again, after completion of the alteration.
    Transaction Semantics TERADATA          Character Set EBCDIC
    Platform LINUX                          Collation ASCII
    Default Character DataType UNICODE      Version Number 17.20
    SPL Text N                              Warning Option N
    Default Database testsp

    Note that the compile time options are changed as specified, but the character set of the procedure has not changed. Version Number 17.20 indicates that the procedure has been upgraded to Analytics Database 17.20.

Example: Suppressing Compilation Warnings with ALTER PROCEDURE

This example shows how compilation warnings can be suppressed using the NO WARNING option of the ALTER PROCEDURE statement by using the following procedure.

  1. Create a new procedure spAP3.
  2. Compile the procedure spAP3:
         REPLACE PROCEDURE testsp.spAP3 ()
         BEGIN
           DECLARE var1 INTEGER DEFAULT 07;
           SELECT ErrorCode INTO :var1 
           FROM dbc.errormsgs 
           WHERE ErrorCode = 5526;
           SET var1 = var1 + 1;
         END;
         BTEQ> .COMPILE FILE spAP3.spl 

    Vantage returns the following report.

         *** Procedure has been created 4 Errors/Warnings. 
         *** Warning: 5527 Stored Procedure Created with Warnings.
         *** Total elapsed time was 2 seconds.
           .COMPILE FILE spAP3.spl 
                                 $
         *** SQL Warning 5802 Statement is not ANSI.
         Warnings reported during compilation
         -------------------------------------------------------
         SPL5000:W(L1), W(5802):Statement is not ANSI.
         SPL5000:W(L3), W(5802):Statement is not ANSI.
         SPL5000:W(L5), W(5802):Statement is not ANSI.
         SPL5000:W(L6), W(5802):Statement is not ANSI.
         -------------------------------------------------------
  3. Recompile the procedure with NO WARNING option.
         ALTER PROCEDURE spAP3 
         COMPILE WITH NO WARNING;

    When this request is successfully performed, the procedure spAP3 is recompiled, but no compilation warnings are displayed.