Recompiling a Stored Procedure | Teradata Vantage - Recompiling a Stored Procedure - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

You must recompile your stored procedures whenever you upgrade or migrate to or across a major release. Use the ALTER PROCEDURE statement to recompile a stored procedure.

AT TIME ZONE Option for SQL Procedures

When you create an SQL procedure, Teradata Database stores the current session time zone for the procedure along with its definition to enable the SQL control language elements and the SQL statements in the procedure to execute in a consistent time zone and produce consistent results. However, time or timestamp data passed as an input parameter to the procedure still use the runtime session time zone rather than the creation time zone for the procedure.

The AT TIME ZONE option enables you to reset the time zone for all of the SQL elements of SQL procedures when you recompile a procedure. Teradata Database then stores the newly specified time zone as the creation time zone for the procedure.

You can only specify AT TIME ZONE with the COMPILE [ONLY] option, and it must follow the COMPILE [ONLY] specification. If it does not, Teradata Database aborts the request and returns an error to the requestor.

Limitation on the Use of TimeZone Strings

When using standard Teradata Database system time zone strings, no time zone rules are enforced for the years 1986 and before, for example, DST shifts. Valid years for Teradata Database standard time zone strings are 1987 through 9999.

If Teradata Database standard time zone strings do not meet your requirements, you can add a new custom time zone string or modify an existing string by modifying or adding new rules to GetTimeZoneDisplacement.

For more information about ALTER PROCEDURE, see “ALTER PROCEDURE (SQL Form)” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.