CREATE/REPLACE PROCEDURE SQL Statement | Teradata Vantage - CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form) - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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