SQL Statements in Stored Procedures | Teradata Vantage - Rules for Using SQL Statements in Stored Procedures - 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™

The rules governing the use of any statement within a stored procedure, including static and dynamic SQL statements, control statements, condition handler, cursor declaration, condition declaration, and local declaration statements, depend on the option specified in the SQL SECURITY clause.

The following rules apply to the use of statements within a stored procedure:

If any SQL statement specified in the stored procedure references a missing database object and the SQL security clause is CREATOR, OWNER, or INVOKER, an SPL compilation warning is reported during the procedure creation.

If any SQL statement specified in the stored procedure references a missing database object and SQL security clause is DEFINER and:
  • the creator and owner are the same, an SPL compilation warning is reported during the stored procedure creation.
  • the creator and owner are not the same, an error is reported and the stored procedure is not created.

If the referenced object does not exist when the stored procedure is executed, a runtime exception is reported.

If the cursor SELECT statement references a missing database object, an SPL compilation error is reported.

When the object created by an SQL statement inside the stored procedure body already exists, or exists with a different schema, an SPL compilation warning is reported.

If the user does not have the required privileges on the objects referenced in the stored procedure, appropriate warnings or errors are reported during stored procedure creation. Teradata Database checks the privileges based on the definition of the SQL SECURITY clause.

If the required privileges do not exist when the stored procedure is executed, a runtime exception is reported.

If the creator does not have the required privileges on the objects referenced in the cursor SELECT statement, an SPL compilation error is reported.

Ownership of Objects Created by Stored Procedures

  • The immediate owner of the stored procedure is the creator of permanent objects created through the stored procedure. This is true even if you are accessing Teradata Database through a proxy connection. A volatile table is not a permanent object, and hence an exception.

    Other users executing the stored procedure do not get any automatic rights on the newly created objects. The immediate owner can explicitly grant privileges on the newly created objects to other users.

  • If a database object in an SQL statement is not explicitly qualified by a database name, the default database used to implicitly qualify the object depends on the SQL SECURITY option.

    If a DDL statement is creating the database object, the qualifying database (either implicit or explicit) is the immediate owner of the object created.

SQL Statement Errors

Errors and warnings resulting from any statement within the stored procedure body have the following impact:
WHEN This Occurs in Any Statement … THEN …
syntax error
  • a compilation error is reported.
  • the procedure is not created.
more than one error only the first error is reported for that statement.
more than one warning only the first warning is reported for that statement.
errors and warnings only the first error is reported for that statement.
compilation warning(s), but no errors the stored procedure is created with warnings.

Unqualified Objects in SQL Statements

During stored procedure execution, the following rules apply to database objects referenced in any DML statement, and not explicitly qualified by a database name.
  • An unqualified table reference defaults to the default database of the stored procedure. The default database depends on the SQL SECURITY option.
IF … THEN …
no such table exists in the compile-time default database the system looks for a volatile table with the same name in the login database for the user.
  • If the volatile table exists, then it is accessed.
  • If the volatile table does not exist, runtime exception 3807 (Table/view/trigger/procedure does not exist) is reported.
the referenced table exists in the default database the table is accessed, if a volatile table with the same name does not exist in the login database for the user.
runtime exception 3806 (Table/view/trigger name is ambiguous) is reported, if a volatile table with the same name also exists in the login database for the user.
  • All unqualified database objects referenced in the statements specified in the stored procedure body, including references to potential volatile tables, are verified from the current default database.

Related Topics

For more information about SQL SECURITY, see Checking Privileges for Stored Procedures.