CREATE/REPLACE PROCEDURE SQL Statement | Teradata Vantage - 17.10 - CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Directs the SQL procedure compiler to create or replace a procedure from the SQL statements in the remainder of the source text and creates the SQL definition used to invoke the procedure.

REPLACE PROCEDURE directs the SQL procedure compiler to replace the definition of an existing SQL procedure.

If the specified SQL procedure does not exist, REPLACE PROCEDURE creates a new procedure by that name from the SQL statements in the remainder of the source text.

ANSI Compliance

This statement is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges: CREATE PROCEDURE

You must have the CREATE PROCEDURE privilege on the containing database or user for the procedure. The creator of an SQL procedure is automatically granted the DROP PROCEDURE and EXECUTE privileges WITH GRANT OPTION.

Vantage checks SQL procedure privileges differently depending on the definition of the SQL SECURITY clause option for the procedure.

Note that you cannot specify the OWNER option for the SQL SECURITY clause unless you have the explicitly granted CREATE OWNER PROCEDURE privilege to permit you to create an SQL procedure in a database or user other than your default database or user. This is an extremely important privilege for the Security Administrator to safeguard, ensuring that only those users who absolutely require the privilege be granted it.

If the parameter type is a UDT or an Array/Varray with a UDT element type, you must have at least one of the following privileges:
  • UDTUSAGE on the specified UDT
  • UDTUSAGE on the SYSUDTLIB database
  • UDTTYPE on the SYSUDTLIB database

For more information, see Rules for SQL Procedure Privileges.

Required Privileges: REPLACE PROCEDURE

You must have the DROP PROCEDURE privilege on the procedure or its containing database or user to replace it.

You must have the CREATE PROCEDURE privilege on the procedure or its containing database or user if it does not already exist.

The user replacing a procedure must have the privileges for all objects it accesses.

Once a procedure has been replaced, its immediate owner is its containing database or user, not the user who replaced it. The immediately owning database must have all the appropriate privileges for executing the procedure, including WITH GRANT OPTION.

Privileges Granted Automatically

When you create a new procedure, Vantage automatically grants the following privileges on it.

  • DROP PROCEDURE
  • EXECUTE PROCEDURE