Procedure for Creating an SQL Procedure | CREATE PROCEDURE | Teradata Vantage - Procedure for Creating an SQL Procedure - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

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
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

Use the following procedure to create an SQL procedure. Steps 4, 5 and 6 apply only when a compound statement is specified.

  1. Identify and specify the procedure name after the keywords CREATE PROCEDURE. The procedure_name can optionally be qualified with database_name.
  2. Identify the parameters, if desired, and specify them immediately after the procedure name.
    The entire set of parameters is in a comma-separated list, and must be enclosed by LEFT PARENTHESIS and RIGHT PARENTHESIS characters.
    • Each parameter consists of 3 elements, in the following left-to-right order.
    • Parameter type (optional)
    • Parameter name (mandatory)
    • Data type of the parameter (mandatory)

      You cannot specify a character parameter data type with a server character set of KANJI1. Otherwise, the database aborts the request and returns an error to the requestor.

  3. Specify either a single statement to perform the main task, or the BEGIN keyword for a compound statement.

    If you have specified a single statement, terminate that with a final SEMICOLON character to complete the CREATE PROCEDURE statement.

    If you have started to specify a compound statement, go to step 4.

  4. Following the BEGIN keyword, specify any variable declarations, cursor declarations, and condition handler declarations, in that order.

    Within each handler declaration, specify a single statement or a BEGIN-END compound statement as the handler action.

  5. Specify the statements to perform the main tasks.

    These statements must be specified after the variable, cursor, and condition handler declarations.

  6. Terminate the CREATE PROCEDURE statement with a final SEMICOLON character after the END keyword.

    You can specify an optional ending label for the BEGIN-END block after the END keyword. If you specify the ending label, then you must specify an equivalent beginning label suffixed with a COLON (:) character before the BEGIN keyword.