ALTER PROCEDURE Examples | Teradata Vantage - Examples: ALTER PROCEDURE (SQL form) - Advanced SQL Engine - Teradata Database

SQL Procedure for Use in Examples

The examples in this section refer to the SQL procedure spAP2, shown below:

    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;
        *** 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 created in Teradata Database 13.0 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 09
   SPL Text Y                              Warning Option Y
   Default Database testsp

   The Version Number 09 indicates that the procedure was created in Teradata Database 13.0.

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 11
   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 11 indicates that the procedure has been upgraded to Teradata Database 14.0.

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.