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:
- Compile the procedure spAP3 in a Teradata session mode session.
BTEQ> .COMPILE FILE spAP3.spl
- Change the session mode.
.LOGOFF .SET SESSION TRANS ANSI .LOGON testsp, password
- 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.
- Compile the procedure spAP2.
.COMPILE FILE spAP2.spl WITH NOSPL
- 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
- 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.
- 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.
- Using BTEQ, change the session character set from EBCDIC to ASCII.
.SET SESSION charset 'ASCII'
- Execute an ALTER PROCEDURE request that specifies the NO SPL and NO WARNING options.
ALTER PROCEDURE spAP2 COMPILE WITH NO SPL, NO WARNING;
- 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.
- Create a new procedure spAP3.
- 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. -------------------------------------------------------
- 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.